Pass variable to Openquery

• Marcus Farrugia

  SSCarpal Tunnel

  Points: 4718

  More actions

  October 26, 2012 at 7:57 am

  #266126

  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 + '')

• Chandra Sekhara Vyas Dhara

  SSChampion

  Points: 10207

  More actions

  October 26, 2012 at 8:10 am

  #1553191

  Try the following syntax

  declare @string varchar(max)

  set @string = 'abcd'

  select * from openquery

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

• Marcus Farrugia

  SSCarpal Tunnel

  Points: 4718

  More actions

  October 26, 2012 at 8:14 am

  #1553195

  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?

• Elliott Berkihiser

  SSCarpal Tunnel

  Points: 4068

  More actions

  October 26, 2012 at 10:13 am

  #1553281

  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

• Sean Pearce

  SSCoach

  Points: 15999

  More actions

  October 29, 2012 at 6:50 am

  #1553706

  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]

• Elliott Berkihiser

  SSCarpal Tunnel

  Points: 4068

  More actions

  October 29, 2012 at 8:33 am

  #1553741

  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

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  October 29, 2012 at 8:39 am

  #1553746

  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.

  ---

• Elliott Berkihiser

  SSCarpal Tunnel

  Points: 4068

  More actions

  October 29, 2012 at 9:14 am

  #1553764

  Yes, I agree.

• sagarkondoori

  Newbie

  Points: 1

  More actions

  August 30, 2019 at 7:48 am

  #3675636

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