SQL Server 2012 :: Open Query To PostGres Passing Parameters?

Mar 24, 2014

Looking to pass in the @targetDbName into the Open Query.

The target DB is PostGres and requires 2 single quotes around the dataset name.

I have tried many possible variations using the '+ @variableName +'

USE JonathanDB
declare @dqzDateVer int;
declare @targetDbName varchar(25);
select @targetDbName = DB_NAME();
select @dqzDateVer = dqz_date_ver FROM OPENQUERY(ofr_meta_db, 'select dqz_date_ver from ofr_registry.dataset_feed_state where dataset_name=''JonathanDB'' and state = ''Done'' order by row_iddesc limit 1');
print @dqzDateVer ;
print @targetDbName;

View 1 Replies


ADVERTISEMENT

SQL Server 2012 :: Passing Parameters To Table Valued Function?

Mar 13, 2014

I am creating a function where I want to pass it parameters and then use those parameters in a select statement. When I do that it selects the variable name as a literal not a column. How do I switch that context.

Query:

ALTER FUNCTION [dbo].[ufn_Banner_Orion_Employee_Comparison_parser_v2]
(
@BANNER_COLUMN AS VARCHAR(MAX),
@ORION_COLUMN AS VARCHAR(MAX)
)
RETURNS @Banner_Orion_Employee_Comparison TABLE

[code]....

Returns:

I execute this:

select * from ufn_Banner_Orion_Employee_Comparison_parser_v2 ('a.BANNER_RANK' , 'b.[rank]')

and get:

CerecerezNULLNULLa.BANNER_RANKb.[rank]

View 7 Replies View Related

SQL 2012 :: Passing Parameters To Backup Job?

Sep 23, 2014

I am using Ola's solution for db backups by the way and its main command is something like this:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DBgrowth -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'MyDatabase1, MyDatabase2', @Directory = N'serverpath', @BackupType = 'FULL', @BackupSoftware = 'SQLBACKUP', @Compress = 'Y', @CompressionLevel = 2, @NumberOfFiles = 5, @Verify = 'N', @CleanupTime = 504, @CheckSum = 'N', @LogToTable = 'Y'" -b

where MyDatabase1 and MyDatabase2 are clients abover 100GB.

Now, I would like to make this dynamic, no user intervention. I already have a table where I'm keeping current and historical data of db sizes.

How can I make this job dynamic, so when a new database passes the 100GB threshold, their names will be part of @Databases parameter? In other words, added automatically, based on my query.

View 7 Replies View Related

Passing Params To SP Using OPEN QUERY

Oct 8, 2014

How to pass parameter values to Stored Procedure using Openquery ?

DECLARE @CenterNumber nvarchar(8)
DECLARE @CenterName nvarchar(100)
DECLARE @tblLeads table(
LeadCount int)
SET @CenterNumber = '98454152'
SET @CenterName = 'neck'

[code]....

View 3 Replies View Related

SQL 2012 :: SSIS Passing Parameters To Stored Procedure That Changes Based On The Data Being Passed?

Jun 23, 2015

Using the following:

SQL Server: SQL Server 2012
Visual Studio 2012

I have created an SSIS package where I have added an Execute SQL Task to run an existing stored procedure in my SQL database.

General Tab:

Result Set: None
Connection Type: OLE DB
SourceType: Direct Input
IsQueryStoredProcedure: False (this is greyed out and cannot be changed)
Bypass Prepare: True
SQL Statement: EXEC FL_CUSTOM_sp_ml_location_load ?, ?;

Parameter Mapping:

Variable Name Direction Data Type Prmtr Name Prmtr Size
User: system_cd Input NVARCHAR 0 10
User: location_type_cd Input NVARCHAR 1 10

Variables:

location_type_cd - Data type - string; Value - Store (this is static)
system_cd - Data type - string - ??????
The system code changes based on the system field for each record in the load table

Sample Data:

SysStr # Str_Nm
3 7421Store1
3 7454Store2
1815061Store3
1815063Store4
1615064Store5
1615065Store6
1615066Store7
7725155Store8

STORED PROCEDURE: The stored procedure takes data from a load table and inserts it into another table:

Stored procedure variables:
ALTER PROCEDURE [dbo].[sp_ml_location_load]
(@system_cd nvarchar(10), @location_type_cd nvarchar(10))
AS
BEGIN .....................

This is an example of what I want to accomplish: I need to be able to group all system 3 records, then pass 3 as the parameter for system_cd, run the stored procedure for those records, then group all system 18 records, then pass 18 as the parameter for system_cd, run the stored procedure for those records and keep doing this for each different system in the table until all records are processed.

I am not sure how or if it can be done to pass the system parameter to the stored procedure based on the system # in the sys field of the data.

View 6 Replies View Related

Passing Parameters To AS400 Query

Dec 5, 2007

Hello,

I'm using

IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider
to extract data from AS400 and copy them to SQLServer2005. It's my first experience with exptracting data from AS400.

I need to pass in SSIS a parameter to the extract query like
SELECT dtses
FROM oslglf3.flp016
WHERE dtses > ?

I'm naming the parameter in the mapping as 1 but geetting an error
[as400 [1]] Error: The SQL command requires a parameter named ""00001"", which is not found in the parameter mapping.

I have tried 00001, '00001', ("00001" is not accepted from SSIS) but getting still this error.

A query like
SELECT dtses
FROM oslglf3.flp016
WHERE dtses > '1071205'
works OK.

Can anybody help me ?

thanks in advance

View 7 Replies View Related

SqlDataSource And Passing Multiple Int Parameters In Query

Aug 11, 2007

Hi All,

View 2 Replies View Related

Urgent: Passing Parameters From Vb To Access Query

Jan 12, 2004

hi all,

I have a view (query) created in ms acess. how can i pass a parameter from vb to the query at runtime? can i do it?

View 7 Replies View Related

Is There A Way To Loop Over Report Parameters, And Format Them Before Passing Them Onto The Query

Nov 13, 2007

Good morning all,


I have a report which is getting its parameters from an ASP.net page. My ASP developer wants to send in simple values, such as the list 1,2,3,4 for a parameter. However my report needs that list to look like [CD RSRC].[RSRC].&[1], [CD RSRC].[RSRC].&[2], [CD RSRC].[RSRC].&[3], [CD RSRC].[RSRC].&[4].


Is there any way, on the report services side, to capture an incoming report parameter, parse it, loop over the parsed values and format them?


I don't think there is, but I wanted to check before I go back to the developer and tell him he has to send in tuple lists.


Thanks,
Kathryn

View 1 Replies View Related

Reporting Services :: Passing Report Parameters To A Query Using WCF Data Source?

Nov 29, 2010

I am attempting to pass report parameters to my query. My report's data source is a WCF web service. I can run normal queries fine, but I have found that without the know-how to pass parameters to my query I am limited with my capabilities.

Assume a string parameter named "Name" with a specified default value of "Jmachol90", how would I pass that into the following query at the designated place:
  
<Query>
          <Method Namespace="http://schemas.microsoft.com/sqlserver/masterdataservices/2009/09" Name="SecurityPrincipalsGetRequest">
          <Parameters>
          <Parameter Name="Criteria" Type="XML">
<DefaultValue>

[code]....

View 17 Replies View Related

T-SQL (SS2K8) :: Passing Parameters On Query - Error Converting Data Type Varchar To Numeric

Sep 1, 2014

I have the following code and i want to passed more than one value:

DECLARE @myvendedor AS varchar(255)
SET @myvendedor = '87,30'
print @myvendedor
SELECT top 10 ECOM.COM1,* from ecom (nolock) WHERE ecom.PORVEND=1 AND ECOM.VENDEDOR IN (@myvendedor)
Table Field ECOM.VENDEDOR is Numeric(4,0)

This error occur:

87,30 --Result of PRINT

Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to numeric.

I change :

DECLARE @myvendedor AS numeric(4,0)

and this error appear:

Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to numeric.

View 9 Replies View Related

SQL Server 2008 :: SSRS Passing Parameters In URL?

May 20, 2015

i have a url that opens my report with no entries in the parameters i then add &Search=96200 at the end of the URL and still the parameter is blank

View 2 Replies View Related

Reporting Server - Passing Parameters Programtically

Jun 28, 2007

We've developed several reports where you can type in / select variables and run them, affecting their outcomes.



We'd like to change this so if the user clicks on a link from our Intranet, this takes them straight to the report with the appropriate parameter(s) - rather like the "jump to report" function within a report - i.e. the url for that function...


Is this possible?



Steve

View 4 Replies View Related

Linked Server To Postgres

Mar 18, 2004

Hi

Can anyone help me with this.. driving me nuts.
when setting up a linked server to Postgres 7.3.1 (specifics*) I can see the tables in Enterprise Manager but in Query Analyzer i get this error when doing the below queries

SELECT * FROM OPENQUERY(POSTGRES_1,'select * from dbo.TestTable')

OR

SELECT * FROM OPENQUERY(POSTGRES_1,'select * from TestTable')

Error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'select id,value_char from dbo.Test_Default_1'. The OLE DB provider 'MSDASQL' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='MSDASQL', Query=select id,value_char from dbo.Test_Default_1'].

View 6 Replies View Related

SQL Server Connecting To PostGres

May 13, 2008

We have a third party application that we will be upgrading.
The current version is using a MYSQL database, while the new version is using PostGresSQL.

We have an external database SQLServer 2005 that currently pulls data from the MySQL database into the SQL Server.
I now have to setup the same pull but from PostGresSQL to the SQL Server, how would I go about doing that?
Is there a driver I would need? Ports that would need to be opened?

Thanks



View 7 Replies View Related

Transferring Data From Postgres 8.x Databases To SQL Server 2005

Feb 4, 2007

I have a requirement to pull data from several large tables residing in a Postgres 8.x (PG) database into our SQL Server 2005 warehouse every night. I have been doing this via data dumps to a file then bulk loading said file into SQL2005 but I am running into character encoding issues and newlines embedded in some of the strings which is causing the bulk loader some problems.

I would like to take advantage of the power of SSIS to move the data. I cannot find a current working OLEDB driver for PG and I cannot find an ODBC Data Flow Source in SSIS to use the ODBC driver that is available for PG so I am stuck.

If someone would be kind enough to shed some light on whether this is possible and if so where I could get the instructions on how to do it I would be most appreciative.

Thanks.

--sean

View 3 Replies View Related

SQL Server 2012 :: Variable Not Passing Through To Expression

May 22, 2014

I have created a variable in a SQL Task and assigned it to a string variable. When I debug the container with a breakpoint, I can see the correct date value being assigned to the variable.

I have an ADO Net source setup to an Oracle connection. I need to pull the Oracle data down that has an updated date greater than the updated date in my ODS.

My issue is that the variable is not being passed through to my expression that I use for an ADO Net source.

"SELECT * FROM BI_EDW.GL_JE_HEADERS WHERE LAST_UPDATE_DATE > To_Date('" + (DT_WSTR, 19) @[User::varLastUpdateDate] + "','yyyy-mm-dd hh24:mi:ss')"

View 2 Replies View Related

SQL Server 2012 :: Passing Sum To Stored Procedure

Sep 21, 2015

Is it possible to pass a sum of vars to a SP ?I've tried this but it gives me an error

exec mysp
@param = (@var1 + @var2)

View 1 Replies View Related

SQL Server 2012 :: Open Rowset With Non CSV Or TXT Extension

Jul 18, 2014

My goal is to have a file (it comes from another system) that I want to import via an OPENROWSET style query.

The query would look like this:

select [NoName] from openrowset('MSDASQL'
,'Driver={Microsoft Access Text Driver (*.txt, *.csv)};
DefaultDir=c:filedir'
,'select * from "file.lst"')

If I make the file a .csv it works fine. However, if it has a not CSV or TXT extension it throws the following error and cannot seem to find a solution to it.

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver] Cannot update. Database or object is read-only.". Msg 7350, Level 16, State 2, Line 1 Cannot get the column information from OLE DB provider "MSDASQL" for linked server "(null)".

In addition, (although I can probably find this elsewhere), I need to have the first line 'BLANK' so that it does not miss data (there is no header row). Is there a way to use OPENROWSET without BULK to basically include all rows as data?

View 0 Replies View Related

SQL Server 2012 :: Error When Passing Multiple Rows

Apr 20, 2015

I am getting error when I passed multiple rows in less than condition:

create table #t1
( ID int)
INSERT INTO #t1

SELECT 1 UNION ALL SELECT 5 UNION ALL SELECT 8
CREATE TABLE #t2
(ID int)
INSERT INTO #t2
SELECT 3 UNION ALL SELECT 20 UNION ALL SELECT 4

SELECT ID FROM #t2
WHERE ID < (SELECT ID FROM #t1)

Error is: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

How to pass multiple values in this condition?

View 2 Replies View Related

SQL Server 2012 :: Passing Date Parameter To Stored Procedure?

Oct 9, 2014

I am trying to pass a date parameter to a stored procedure.

I pass the actual date as a string but keep getting errors that the string variable cannot be converted to a date whatever I do.

Basically, this works:

DECLARE @DDate DATETIME
SET @DDate = convert(datetime, '2004-01-01',101 )

But this returns an error:

DECLARE @strdate VARCHAR
SET @strdate = '2004-01-01'
DECLARE @DDate DATETIME
SET @DDate = convert(datetime, @strdate,101 )

What am I doing wrong?

View 8 Replies View Related

SQL Server 2012 :: Open EDI File And Insert Data Into Tables

Sep 27, 2012

Is there a way to open EDI file in SQL Server and insert the data into tables?

View 9 Replies View Related

Unable To Open New Query Window Or Open Any Table

Nov 3, 2014

Post installation of SQL Server 2014 Express edition, I am able to connect to the Database Instance.

But while opening a new query window in SSMS or opening a table getting the error:

Package 'RadLangSvc.Package, RadLangSvc, Version 12.0.0.0, Culture=Neutral, Public Token=89845dcd8080cc91' failed to load

Object reference not set to an instance of an object. (mscorlib)..Have already tried installing the componentsDACProjectSystemSetup_enu.msi, TSql LanguageService_enu.msi, DACFramework_enu.msi from path VS 2010 WCU DAC.

View 5 Replies View Related

SQL Server 2012 :: Modify Stored Procedure Opens Does Not Open For Modifications As Earlier

Jan 5, 2015

when i click on the modify option on a stored procedure it is not opening for editing as earlier. it opens as if for scripting.for example i clicked modify procedure on stored proc. "text" and the result is as follows.

/****** Object: StoredProcedure [dbo].[test] Script Date: 05/01/2015 7:50:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test]') AND type in (N'P', N'PC'))

[code]....

View 4 Replies View Related

Parameters Passing

Mar 14, 2008

Hi,
 how to pass paremeters in sqldataadapter? here iam checking authentication using sqlcommand and datareader
like that if iam checking authentication using dataset and sqldatareader how to use parameters?
 
SqlConnection cn = new SqlConnection("user id=sa;password=abc;database=xyz;data source=server");
cn.Open();
// string str = "select username from security where username='" + TextBox1.Text + "'and password='" + TextBox2.Text + "'";
string str = "select username from security where username=@username and password=@password";
SqlCommand cmd = new SqlCommand(str, cn);
cmd.Parameters.AddWithValue("@username", TextBox1.Text);
cmd.Parameters.AddWithValue("@password", TextBox2.Text);
SqlDataReader dr = cmd.ExecuteReader();

if (dr.HasRows)
{
Session["username"] = TextBox1.Text;
FormsAuthentication.RedirectFromLoginPage(TextBox1.Text, false);


}
else
{
Label1.Visible = true;
Label1.Text = "Invalid Username/Password";
}
-------------------------------------------------------------------------------------
like that if iam checking authentication using dataset and sqldatareader how to use parameters?SqlConnection cn = new SqlConnection("user id=sa;password=abc;database=xyz; data source=server");
cn.Open();ds = new DataSet();
string str = "select username from security where username=@username, password=@password";da = new SqlDataAdapter(str, cn);  //how to pass parameters for that
da.Fill(ds, "security");
Response.Redirect("dafault2.aspx");

View 2 Replies View Related

Passing Parameters To DTS In C#

Apr 17, 2004

I have a dts package file. I am able to execute the file thro C#. I want to pass userid, password, datasource through program. How to do this?

DTS.PackClass package = new DTS.PackClass();

string filename = @"c:abc.dts";
string password = null;
string packageID = null;
string versionID = null;
string name = "DTSPack";
object pVarPersistStfOfHost = null;

package.LoadFromStorageFile(filename, password, packageID,
versionID, name, ref pVarPersistStfOfHost);
package.Execute();
package.UnInitialize();
package = null;


Thanks
Jtamil

View 2 Replies View Related

HELP! Passing Parameters

May 30, 2006

Still need help passing a criteria parameter query from a SQL Function (i.e. @StartDate) to a report header in Access Data Project where header = 'Transactions As Of [StartDate].

If anyone knows anywhere I can get help on this, I would really appreciate it. Thanks.

View 1 Replies View Related

Passing Parameters In TableAdapter

Jul 28, 2006

A little new to ASP.NET pages, and I'm trying to pass some parameters to a SQL 2000 Server using a TableAdapter, code is as follows:
------
TestTableAdapters.test_ModemsTableAdapter modemsAdapter = new TestTableAdapters.test_ModemsTableAdapter();
// Add a new modem
modemsAdapter.InsertModem(frmRDate, frmModem_ID, frmProvisioning, strDecESN );
--------
And the error I get when loading the ASP.NET page is as follows:
Compiler Error Message: CS1502: The best overloaded method match for 'TestTableAdapters.test_ModemsTableAdapter.InsertModem(System.DateTime?, string, string, string)' has some invalid arguments
---------------
Now I realized the four strings that I am trying to pass to the server refer to ID's on Textboxes on the web page. Not sure if that might be the problem for databinding... ? Or is it my statement for the adapter?
-Ed

View 4 Replies View Related

Passing Parameters Using IN Statement

Dec 7, 2006

The SQL for a dataset in ASP.NET 2.0 is as follows....
SELECT DISTINCT StudentData.StudentDataKeyFROM         Student2Roster INNER JOIN                      StudentData ON Student2Roster.StudentDataRecID = StudentData.StudentDataRecIDWHERE     (Student2Roster.ClassRosterRecID IN (@ClassRosterRecIDs)
ClassRosterRecIDs are giuds
At design time i use 'b2cf594d-908b-4c0c-a67f-6364899a4d42', '2e0b3472-d3f0-4a54-94af-bfc0a99525d9' as the parameter and it works in the designer but not at runtime.
At runtime I get the error Conversion failed when converting from a character string to uniqueidentifier.
If I leave the quotes off and only use 1 value like b2cf594d-908b-4c0c-a67f-6364899a4d42 it works.How can I use multiple guids as an IN parameter like 'b2cf594d-908b-4c0c-a67f-6364899a4d42', '2e0b3472-d3f0-4a54-94af-bfc0a99525d9'?
Thanks

View 2 Replies View Related

Passing Parameters To LIKE Function

Jul 22, 2007

 Hi,I want to pass parameter in to LIKE functionIs there anyway to do thatThanks,Janaka  

View 6 Replies View Related

Passing Dtae Parameters

Apr 10, 2008

Hi
I'm trying to add 2 date parameters to the where caluse but not sure how to structure it:
 Can anybody help?
 Public Function GetCategoryPerformance(ByVal PriorityType As String) As DataSet
'*******************************************************
'This web service will return a specific row for a table
'*******************************************************Dim cn As New SqlConnection("data source=CADTRAINING;persist security info=True;initial catalog=pathwaysreporting;user id=cad;password=cad;")
Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT * from sp_category_performance where where priority = '" & PriorityType & "'", cn)Dim ds As DataSet = New DataSet
Try
da.Fill(ds, "CategoryPerformance")Catch ex As Exception
Throw New Exception(ex.Message)
End TryReturn ds
End Function
End Class

View 2 Replies View Related

Passing Parameters To A DTS Package

Mar 10, 2005

Hello All,

I am executing a DTS package from an asp page using the following code. I would like to also pass DTS Global variables along. i assume this is possible but can't seem to find an example.


Set oPkg = Server.CreateObject("DTS.Package")
oPkg.LoadFromSQLServer "HOFDBMCRM4","TraubGar","ripley",DTSSQLStgFlag_Default,"","","","DSC_CalculateBOS"
oPkg.Execute()


Thanks, Gary

View 1 Replies View Related

Passing Parameters In ISQL

Aug 30, 2001

Hi,

I would like to pass parameters from isql command line to an input query file. I am working on SQL Server 7.0. How can I accomplish this?

My eg. is like this:

> isql.exe (all the server options etc.,) /iInputfile /oOutput file
I need the output depend on the query given in the input file with the query depending on the parameter I supply on the command line.Thanks for your help in advance.

thanks,
Sravan.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved