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;
-
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
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