August 21, 2013 at 2:50 pm
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 = ''''
')
August 21, 2013 at 2:58 pm
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
August 21, 2013 at 3:08 pm
No, i cannot .
August 21, 2013 at 3:31 pm
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
August 21, 2013 at 4:26 pm
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.
August 21, 2013 at 5:07 pm
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
August 21, 2013 at 5:54 pm
--
August 21, 2013 at 9:59 pm
Can you check this link.
August 21, 2013 at 11:17 pm
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
Change is inevitable... Change for the better is not.
August 21, 2013 at 11:19 pm
SQL Show (8/21/2013)
http://support.microsoft.com/kb/314520%5B/quote%5D
Z'actly!
--Jeff Moden
Change is inevitable... Change for the better is not.
August 23, 2013 at 6:48 am
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
August 23, 2013 at 10:47 am
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
August 23, 2013 at 11:03 am
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
Change is inevitable... Change for the better is not.
August 23, 2013 at 11:04 am
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.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply