Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Pass variable to Openquery
Add to briefcase

Pass variable to Openquery Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Marcus Farrugia
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, May 02, 2013 6:08 AM
Points: 139, Visits: 276
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
vyas
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: Thursday, May 16, 2013 4:02 AM
Points: 3,131, Visits: 1,056
Try the following syntax

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


Post #1377607
Marcus Farrugia
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, May 02, 2013 6:08 AM
Points: 139, Visits: 276
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
Elliott Berkihiser
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: Saturday, November 17, 2012 9:59 AM
Points: 256, Visits: 1,990
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
Sean Pearce
Posted Monday, October 29, 2012 6:50 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:18 AM
Points: 386, Visits: 1,424
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

About Me
Post #1378228
Elliott Berkihiser
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: Saturday, November 17, 2012 9:59 AM
Points: 256, Visits: 1,990
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
Phil Parkin
Posted Monday, October 29, 2012 8:39 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 4,323, Visits: 9,662
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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1378278
Elliott Berkihiser
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: Saturday, November 17, 2012 9:59 AM
Points: 256, Visits: 1,990
Yes, I agree.
Post #1378298
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.