Post reply

how to pass parameters into an openquery ?.

  • 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 = ''''

    ')

  • August 21, 2013 at 2:58 pm

    #1643668

    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

    #1643672

    No, i cannot .

  • August 21, 2013 at 3:31 pm

    #1643686

    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

    #1643699

    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

    #1643705

    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

    #1643712

    --

  • August 21, 2013 at 9:59 pm

    #1643736

    Can you check this link.

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

  • August 21, 2013 at 11:17 pm

    #1643747

    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)

  • August 21, 2013 at 11:19 pm

    #1643748

    SQL Show (8/21/2013)

    Can you check this link.

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

    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)

  • August 23, 2013 at 6:48 am

    #1644379

    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

    #1644492

    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

    #1644495

    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)

  • August 23, 2013 at 11:04 am

    #1644496

    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 14 (of 14 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy