how to pass parameters into an openquery ?.

Question

how to pass parameters into an openquery ?.

weston_086

SSCommitted

Points: 1645
More actions
August 21, 2013 at 2:50 pm

#395342

How i can pass parameters into an openquery ?.
I have openquery it just works fine with the fixed values but i want to pass the values to START_DATE AND END_DATE from variables and then i want to put the result of that to the "dbo.INFORMATION" table.
declare @STDATE as datetime
set @STDATE = cast (GETDATE()-1 as date )
declare @EDATE as datetime
set @EDATE = cast (GETDATE() as date )
insert into dbo.INFORMATION
SELECT * FROM OPENQUERY ([linkedserver],'set fmtonly off Exec [Database].[dbo].[SP_ALL_DAY]
@START_TIME = ''@STDATE'',
@END_TIME = ''@EDATE'',
@RES = ''day'',
@AGG = ''n'',
@FIL =''ALLLIST'',
@COMPANY = ''%'',
@NAME = ''''
')

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

You can't pass as parameters using OPENQUERY. It's a bit of a short-coming in my opinion. Can you make the call to the SP using 4 part naming?

Exec [linkedserver].[Database].[dbo].[SP_ALL_DAY]

@START_TIME = @STDATE,

@END_TIME = @EDATE,

@RES = 'day',

@AGG = 'n',

@FIL ='ALLLIST',

@COMPANY = '%',

@NAME = ''

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

weston_086 SSCommitted Points: 1645 More actions · Answer 2

weston_086

SSCommitted

Points: 1645

August 21, 2013 at 3:08 pm

#1643672

No, i cannot .

Jack Corbett SSC Guru Points: 184393 More actions · Answer 3

Why can't you make the call using 4-part naming?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

weston_086 SSCommitted Points: 1645 More actions · Answer 4

Bcz i want to put the result of the stored proc into "dbo.INFORMATION" table.but it is giving me an error "unable to begin a distributed transactions" when i run using 4 part naming .So thats why i am using the openquery which is working fine with the fixed values. but now i want to pass parameters to the openquery .which is giving me a hard time.I think we can use dynamic sql for that but i don't know how.

Jack Corbett SSC Guru Points: 184393 More actions · Answer 5

Yes, I have seen the same problem, but you can't use dynamic sql inside OPENQUERY because OPENQUERY doesn't allow the use of variables. From BOL:

OPENQUERY does not accept variables for its arguments.

You just need to make sure that DTC is setup correctly to allow for a distributed transaction, http://support.microsoft.com/kb/2027550

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

SQListic SSCertifiable Points: 6904 More actions · Answer 6

SQListic

SSCertifiable

Points: 6904

August 21, 2013 at 5:54 pm

#1643712

--

SQL Show SSCertifiable Points: 6143 More actions · Answer 7

Can you check this link.

http://support.microsoft.com/kb/314520

Jeff Moden SSC Guru Points: 1003921 More actions · Answer 8

weston_086 (8/21/2013)
Bcz i want to put the result of the stored proc into "dbo.INFORMATION" table.but it is giving me an error "unable to begin a distributed transactions" when i run using 4 part naming .So thats why i am using the openquery which is working fine with the fixed values. but now i want to pass parameters to the openquery .which is giving me a hard time.I think we can use dynamic sql for that but i don't know how.

But you CAN make the whole OPENQUERY dynamic.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003921 More actions · Answer 9

SQL Show (8/21/2013)

Can you check this link.

http://support.microsoft.com/kb/314520%5B/quote%5D

Z'actly!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqldriver SSChampion Points: 10440 More actions · Answer 10

I use this with OPENDATASOURCE. I think you can re-purpose the idea to do what you need.

I generally find this method more readable and reasonable for a human being to type than keeping track of an ant colony of single quotes.

CREATE PROC [dbo].[ErikLoadPreLoadFromExcel]

@projectID int,

@FileName varchar(128)='',

@SheetName varchar(100)='Sheet1'

as

DECLARE @PreLoadTable varchar(64)

select @PreLoadTable=ds_PreLoadTable from sample.dbo.ds_dataset where ds_ProjectID=@ProjectID

declare @sqlString as varchar(4000)

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[' + @PreLoadTable +']') AND type in (N'U'))

begin

set @SQLString = 'drop table ' + @PreLoadTable

exec (@SQLString)

end

set @sqlstring='select * into sample.dbo.

from

OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'',

''Data Source=\\p42\SComputing\Sample\SampleRep\[file];

Extended properties="Excel 12.0 XML;HDR=YES;IMEX=1"'')...[[SheetName]$]'

SET @SQLString=REPLACE(@SQLString,'

',@PreLoadTable)

SET @SQLString=REPLACE(@SQLString,'[file]',@FileName)

SET @SQLString=REPLACE(@SQLString,'[SheetName]',@SheetName)

print @sqlString

exec (@SQLString)

GO

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 11

Jeff Moden (8/21/2013)
weston_086 (8/21/2013)
Bcz i want to put the result of the stored proc into "dbo.INFORMATION" table.but it is giving me an error "unable to begin a distributed transactions" when i run using 4 part naming .So thats why i am using the openquery which is working fine with the fixed values. but now i want to pass parameters to the openquery .which is giving me a hard time.I think we can use dynamic sql for that but i don't know how.
But you CAN make the whole OPENQUERY dynamic.

WARNING - see the WARNING statement following this answer!

That's what I do - build up the SQL Statement that contains the OPENQUERY reference as a string and concatenate the variable values into it before executing it.

WARNING: both this method and the method erikd describes are vulnerable to SQL injection. For that reason, I only do this when it's OK to trust the users not to include malicious T-SQL statements in the variable values. Usually, this means "when only I will be executing the query."

Jason Wolfkill

Jeff Moden SSC Guru Points: 1003921 More actions · Answer 12

wolfkillj (8/23/2013)
Jeff Moden (8/21/2013)
weston_086 (8/21/2013)
Bcz i want to put the result of the stored proc into "dbo.INFORMATION" table.but it is giving me an error "unable to begin a distributed transactions" when i run using 4 part naming .So thats why i am using the openquery which is working fine with the fixed values. but now i want to pass parameters to the openquery .which is giving me a hard time.I think we can use dynamic sql for that but i don't know how.
But you CAN make the whole OPENQUERY dynamic.
WARNING - see the WARNING statement following this answer!
That's what I do - build up the SQL Statement that contains the OPENQUERY reference as a string and concatenate the variable values into it before executing it.
WARNING: both this method and the method erikd describes are vulnerable to SQL injection. For that reason, I only do this when it's OK to trust the users not to include malicious T-SQL statements in the variable values. Usually, this means "when only I will be executing the query."

Just to emphasize what's been said above, the warning of possible SQL Injection is always a good one when it comes to dynamic SQL especially since it's still at the top of the list for how people's systems were hacked.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqldriver SSChampion Points: 10440 More actions · Answer 13

wolfkillj (8/23/2013)
Jeff Moden (8/21/2013)
weston_086 (8/21/2013)
Bcz i want to put the result of the stored proc into "dbo.INFORMATION" table.but it is giving me an error "unable to begin a distributed transactions" when i run using 4 part naming .So thats why i am using the openquery which is working fine with the fixed values. but now i want to pass parameters to the openquery .which is giving me a hard time.I think we can use dynamic sql for that but i don't know how.
But you CAN make the whole OPENQUERY dynamic.
WARNING - see the WARNING statement following this answer!
That's what I do - build up the SQL Statement that contains the OPENQUERY reference as a string and concatenate the variable values into it before executing it.
WARNING: both this method and the method erikd describes are vulnerable to SQL injection. For that reason, I only do this when it's OK to trust the users not to include malicious T-SQL statements in the variable values. Usually, this means "when only I will be executing the query."

The day anyone else runs something I write is the day I have some help around here 🙂

I wanted to add to my point that I get more help from this forum of strangers than I do from co-workers who have at least some stake in my success or failure. And to say thank you again to everyone here.