Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Pass variable to Openquery Expand / Collapse
Author
Message
Posted Friday, October 26, 2012 7:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 18, 2014 9:23 AM
Points: 157, Visits: 337
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 + '')
Post #1377598
Posted Friday, October 26, 2012 8:10 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, September 19, 2014 6:08 AM
Points: 3,451, Visits: 1,072
Try the following syntax

declare @string varchar(max)
set @string = 'abcd'
select * from openquery
(TEST_Server, 'select * from Table1 where Field1 = ' + @string + '')



Post #1377607
Posted Friday, October 26, 2012 8:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 18, 2014 9:23 AM
Points: 157, Visits: 337
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?
Post #1377611
Posted Friday, October 26, 2012 10:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 14, 2014 10:08 AM
Points: 256, Visits: 2,007
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
Post #1377713
Posted Monday, October 29, 2012 6:50 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 1:48 AM
Points: 907, Visits: 2,869
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

@SeanPearceSQL

About Me
Post #1378228
Posted Monday, October 29, 2012 8:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 14, 2014 10:08 AM
Points: 256, Visits: 2,007
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

Post #1378271
Posted Monday, October 29, 2012 8:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:58 PM
Points: 5,078, Visits: 11,862
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1378278
Posted Monday, October 29, 2012 9:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 14, 2014 10:08 AM
Points: 256, Visits: 2,007
Yes, I agree.
Post #1378298
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse