Pass variable to Openquery

  • Hi I am getting the following error message trying to pass a variable into an openquery, can someone point out how to correct the error: Incorrect syntax near '+'

    Thank you very much...

    declare @string varchar(max)

    set @string = 'abcd'

    select * from openquery

    (TEST_Server, 'select * from Table1 where Field1 = ' + @string + '')

  • Try the following syntax

    declare @string varchar(max)

    set @string = 'abcd'

    select * from openquery

    (TEST_Server, 'select * from Table1 where Field1 = ' + @string + '')

  • Hi vyas, thank you for the reply. That's the same syntax that I originally had, or at least I can't see the difference in your query, am I missing something?

  • I think the problem is that you are declaring the variable outside the query. Try putting everything into the OpenQuery:

    Select * from OpenQuery(TestServer,'declare @string varchar(max) Set @string=''abcd'' Select * From Table1 where Field1=@string')

    HTH

    Elliott

  • OPENQUERY does not accept variables for its arguments. If you need to build the query dynamically you can use dynamic SQL.

    DECLARE @string VARCHAR(MAX),

    @SQL NVARCHAR(MAX);

    SET @string = 'abcd';

    SET @SQL = 'SELECT * FROM OPENQUERY(TEST_Server, ''SELECT * FROM Table1 WHERE Field1 = ''''' + @string + ''''''')';

    EXEC sp_executesql @SQL;

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • Re: "OpenQuery does not accept variables" --

    This runs fine in my set-up:

    Select * from OpenQuery(MyLinkedServer,'Declare @name varchar(50) Set @name=''RemoteDB'' Select * from sys.databases where name=@name')

    Note the declaration of the variable within the query statement (and the assignment of a value with double quotes).

    HTH

    Elliott

  • Elliott Berkihiser (10/29/2012)


    Re: "OpenQuery does not accept variables" --

    This runs fine in my set-up:

    Select * from OpenQuery(MyLinkedServer,'Declare @name varchar(50) Set @name=''RemoteDB'' Select * from sys.databases where name=@name')

    Note the declaration of the variable within the query statement (and the assignment of a value with double quotes).

    HTH

    Elliott

    In your example, OpenQuery is not 'accepting' a variable. The OpenQuery argument is static, even though it contains a variable.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Yes, I agree.

  • how to pass the IN OPERATOR IN OPENQUERY, I AM NOT GETTING ON INTERNET. PLEASE HELP ABOUT THIS

Viewing 9 posts - 1 through 8 (of 8 total)

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