Results 1 to 5 of 5

Thread: OPENQUERY and local variables

  1. #1
    Jeff Haynes Guest

    OPENQUERY and local variables

    Can anyone suggest how to use local variables in OPENQUERY statements as parameters in the SELECT clause? eg: SELECT * from OPENQUERY(REMOTESERVERNAME,'Select * from table1 where id = @parm1&#39

  2. #2
    Mark Edgar Guest

    OPENQUERY and local variables (reply)

    Try this:

    declare @parm int
    set @parm = 150

    declare @sql varchar(300)
    set @sql = 'select * from openquery(REMOTESERVERNAME,''select * from datatbase1..table1 where col1 = ' + cast(@parm as varchar(5)) + ''&#39'
    select @sql

    exec(@sql)

    This builds a string (@sql) containing your entire query with the @parm variable already resolved to a specific value and then uses the exec function to execute the SQL. The confusing part is knowing when to double up on the single quotes embedded in the sql string. This example also assumes that your local variable is an integer and it converts it to a varchar to allow it to be concatenated into the SQL string. The example uses the three-part table name to ensure that the remote server knows which database the table resides in. You may be able to get away with just using the table name, depending on your environment.


    ------------
    Jeff Haynes at 8/1/00 9:33:53 AM

    Can anyone suggest how to use local variables in OPENQUERY statements as parameters in the SELECT clause? eg: SELECT * from OPENQUERY(REMOTESERVERNAME,'Select * from table1 where id = @parm1&#39

  3. #3
    Jeff Haynes Guest

    OPENQUERY and local variables (reply)

    Thanks Mark

    I did try this but still running into problems:

    I constructed the following:

    DECLARE

    @StartDate DateTime,
    @test varchar(50)

    SET @StartDate = '06/01/2000'
    SET @test = CONVERT(Varchar(30),@StartDate)


    SELECT * From OPENQUERY(EUHORMARS,'SELECT distinct MI1.CONS_SYS_REF, VOYAGES.SERVICE FROM
    MI1 INNER JOIN VOYAGES ON MI1.SHIP_CODE = VOYAGES.SHIP_CODE AND MI1.VOYAGE_NUMBER = VOYAGES.VOYAGE_NUMBER
    WHERE VOYAGES.USER_ACCT_MONTH = (SELECT USER_ACCT_MONTH FROM tblDateLookup WHERE ACCT_MONTH = ''CONVERT(Varchar(30),@StartDate,113)' &#39 ' )


    However I get the error message:

    Server: Msg 241, Level 16, State 3, Line 19
    Syntax error converting DATETIME from character string.

    If I hard code the date (without the local variable) it works!

    eg:

    SELECT * From OPENQUERY(EUHORMARS,'SELECT distinct MI1.CONS_SYS_REF, VOYAGES.SERVICE FROM
    MI1 INNER JOIN VOYAGES ON MI1.SHIP_CODE = VOYAGES.SHIP_CODE AND MI1.VOYAGE_NUMBER = VOYAGES.VOYAGE_NUMBER
    WHERE VOYAGES.USER_ACCT_MONTH = (SELECT USER_ACCT_MONTH FROM tblDateLookup WHERE ACCT_MONTH =''Jun 1 2000 12:00AM'&#39&#39





    ------------
    Mark Edgar at 8/1/00 12:20:35 PM

    Try this:

    declare @parm int
    set @parm = 150

    declare @sql varchar(300)
    set @sql = 'select * from openquery(REMOTESERVERNAME,''select * from datatbase1..table1 where col1 = ' + cast(@parm as varchar(5)) + ''&#39'
    select @sql

    exec(@sql)

    This builds a string (@sql) containing your entire query with the @parm variable already resolved to a specific value and then uses the exec function to execute the SQL. The confusing part is knowing when to double up on the single quotes embedded in the sql string. This example also assumes that your local variable is an integer and it converts it to a varchar to allow it to be concatenated into the SQL string. The example uses the three-part table name to ensure that the remote server knows which database the table resides in. You may be able to get away with just using the table name, depending on your environment.


    ------------
    Jeff Haynes at 8/1/00 9:33:53 AM

    Can anyone suggest how to use local variables in OPENQUERY statements as parameters in the SELECT clause? eg: SELECT * from OPENQUERY(REMOTESERVERNAME,'Select * from table1 where id = @parm1&#39

  4. #4
    Mark Edgar Guest

    OPENQUERY and local variables (reply)

    The problem is most likely related to a single quote problem. You may need to add or remove single quotes to get it to work. Try displaying the results of the sql string using select (select @sql) instead of executing the string to see the exact code you are building.


    ------------
    Jeff Haynes at 8/2/00 8:55:19 AM

    Thanks Mark

    I did try this but still running into problems:

    I constructed the following:

    DECLARE

    @StartDate DateTime,
    @test varchar(50)

    SET @StartDate = '06/01/2000'
    SET @test = CONVERT(Varchar(30),@StartDate)


    SELECT * From OPENQUERY(EUHORMARS,'SELECT distinct MI1.CONS_SYS_REF, VOYAGES.SERVICE FROM
    MI1 INNER JOIN VOYAGES ON MI1.SHIP_CODE = VOYAGES.SHIP_CODE AND MI1.VOYAGE_NUMBER = VOYAGES.VOYAGE_NUMBER
    WHERE VOYAGES.USER_ACCT_MONTH = (SELECT USER_ACCT_MONTH FROM tblDateLookup WHERE ACCT_MONTH = ''CONVERT(Varchar(30),@StartDate,113)' &#39 ' )


    However I get the error message:

    Server: Msg 241, Level 16, State 3, Line 19
    Syntax error converting DATETIME from character string.

    If I hard code the date (without the local variable) it works!

    eg:

    SELECT * From OPENQUERY(EUHORMARS,'SELECT distinct MI1.CONS_SYS_REF, VOYAGES.SERVICE FROM
    MI1 INNER JOIN VOYAGES ON MI1.SHIP_CODE = VOYAGES.SHIP_CODE AND MI1.VOYAGE_NUMBER = VOYAGES.VOYAGE_NUMBER
    WHERE VOYAGES.USER_ACCT_MONTH = (SELECT USER_ACCT_MONTH FROM tblDateLookup WHERE ACCT_MONTH =''Jun 1 2000 12:00AM'&#39&#39





    ------------
    Mark Edgar at 8/1/00 12:20:35 PM

    Try this:

    declare @parm int
    set @parm = 150

    declare @sql varchar(300)
    set @sql = 'select * from openquery(REMOTESERVERNAME,''select * from datatbase1..table1 where col1 = ' + cast(@parm as varchar(5)) + ''&#39'
    select @sql

    exec(@sql)

    This builds a string (@sql) containing your entire query with the @parm variable already resolved to a specific value and then uses the exec function to execute the SQL. The confusing part is knowing when to double up on the single quotes embedded in the sql string. This example also assumes that your local variable is an integer and it converts it to a varchar to allow it to be concatenated into the SQL string. The example uses the three-part table name to ensure that the remote server knows which database the table resides in. You may be able to get away with just using the table name, depending on your environment.


    ------------
    Jeff Haynes at 8/1/00 9:33:53 AM

    Can anyone suggest how to use local variables in OPENQUERY statements as parameters in the SELECT clause? eg: SELECT * from OPENQUERY(REMOTESERVERNAME,'Select * from table1 where id = @parm1&#39

  5. #5
    Jeff Haynes Guest

    OPENQUERY and local variables (reply)

    Thanks again Mark

    You are right in saying that I need to remove or add single quotes. In fact I have added quite a few to get it to work. See this example:

    WHERE USER_ACCT_MONTH = ' + '''''0000''''& #39; + ' AND DATE_OF_SAILING
    >= DateAdd(MM,-1,DATEadd(dd,-1 * DATEPart(dd,(getDate()))+1,GETDATE()))''&# 39;+&#39'


    All is now working fine thanks.


    ------------
    Mark Edgar at 8/2/00 12:18:56 PM

    The problem is most likely related to a single quote problem. You may need to add or remove single quotes to get it to work. Try displaying the results of the sql string using select (select @sql) instead of executing the string to see the exact code you are building.


    ------------
    Jeff Haynes at 8/2/00 8:55:19 AM

    Thanks Mark

    I did try this but still running into problems:

    I constructed the following:

    DECLARE

    @StartDate DateTime,
    @test varchar(50)

    SET @StartDate = '06/01/2000'
    SET @test = CONVERT(Varchar(30),@StartDate)


    SELECT * From OPENQUERY(EUHORMARS,'SELECT distinct MI1.CONS_SYS_REF, VOYAGES.SERVICE FROM
    MI1 INNER JOIN VOYAGES ON MI1.SHIP_CODE = VOYAGES.SHIP_CODE AND MI1.VOYAGE_NUMBER = VOYAGES.VOYAGE_NUMBER
    WHERE VOYAGES.USER_ACCT_MONTH = (SELECT USER_ACCT_MONTH FROM tblDateLookup WHERE ACCT_MONTH = ''CONVERT(Varchar(30),@StartDate,113)' &#39 ' )


    However I get the error message:

    Server: Msg 241, Level 16, State 3, Line 19
    Syntax error converting DATETIME from character string.

    If I hard code the date (without the local variable) it works!

    eg:

    SELECT * From OPENQUERY(EUHORMARS,'SELECT distinct MI1.CONS_SYS_REF, VOYAGES.SERVICE FROM
    MI1 INNER JOIN VOYAGES ON MI1.SHIP_CODE = VOYAGES.SHIP_CODE AND MI1.VOYAGE_NUMBER = VOYAGES.VOYAGE_NUMBER
    WHERE VOYAGES.USER_ACCT_MONTH = (SELECT USER_ACCT_MONTH FROM tblDateLookup WHERE ACCT_MONTH =''Jun 1 2000 12:00AM'&#39&#39





    ------------
    Mark Edgar at 8/1/00 12:20:35 PM

    Try this:

    declare @parm int
    set @parm = 150

    declare @sql varchar(300)
    set @sql = 'select * from openquery(REMOTESERVERNAME,''select * from datatbase1..table1 where col1 = ' + cast(@parm as varchar(5)) + ''&#39'
    select @sql

    exec(@sql)

    This builds a string (@sql) containing your entire query with the @parm variable already resolved to a specific value and then uses the exec function to execute the SQL. The confusing part is knowing when to double up on the single quotes embedded in the sql string. This example also assumes that your local variable is an integer and it converts it to a varchar to allow it to be concatenated into the SQL string. The example uses the three-part table name to ensure that the remote server knows which database the table resides in. You may be able to get away with just using the table name, depending on your environment.


    ------------
    Jeff Haynes at 8/1/00 9:33:53 AM

    Can anyone suggest how to use local variables in OPENQUERY statements as parameters in the SELECT clause? eg: SELECT * from OPENQUERY(REMOTESERVERNAME,'Select * from table1 where id = @parm1&#39

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •