?
Solved

Enclose variable in OPENQUERY SELECT clause

Posted on 2005-03-02
10
Medium Priority
?
808 Views
Last Modified: 2010-05-18
Hi,

As an example we have the following statement :-

SELECT MyValue FROM OPENQUERY(TEST,'SELECT MyValue FROM OTHERTABLE WHERE KeyValue = ''' + @Currency + ''')

...and we wish to use a variable passed to this SPROC in the WHERE clause but when we try we receive the error :-

Line 1: Incorrect syntax near '+'.

Any thoughts?

Thanks.

James.
0
Comment
Question by:JAMES
  • 6
  • 4
10 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13439695
you can try:
'SELECT MyValue FROM TEST.yourdbname.db.OTHERTABLE WHERE KeyValue =  @Currency
0
 

Author Comment

by:JAMES
ID: 13439717
Cant do that as I have to use "OpenQuery" with this Sybase Adaptive Server Anywhere 6 Db im afraid!
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13439742
did you try?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 43

Expert Comment

by:Eugene Z
ID: 13439751
what sql server version\edition do you have?
0
 

Author Comment

by:JAMES
ID: 13439768
When we started down the path of connecting to this legacy db we tried to use that format to no avail.

Thats the reason we went to OPENQUERY the open query route!

Using SQL Server 2000. SP Latest!
0
 
LVL 43

Accepted Solution

by:
Eugene Z earned 2000 total points
ID: 13439894
0
 

Author Comment

by:JAMES
ID: 13439962
Good answer - I will give it a shot and report back!

Thanks.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13440095
try :

----------------------start copy---------------------------
SET QUOTED_IDENTIFIER Off
GO

declare @str varchar(4000)
declare @var varchar(50)
set @var='master'
set @str="SELECT * FROM OPENQUERY(TEST,'SELECT * FROM master..sysfiles WHERE name =''"  + @var + "''')"
print @str
--SELECT * FROM OPENQUERY(TEST,'SELECT * FROM master..sysfiles WHERE name =''master''')
exec (@str)

------------------end copy-----
0
 

Author Comment

by:JAMES
ID: 13440395
Already tried that before crying for help - didnt like it!!

Anyway I think I have it now without using a variable in the OPENQUERY - I moved the WHERE clause to the returned dataset all its fine.

I will award the points anyway as I am sure the dynamic SQL wood have worked.

Thanks.

James.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13440456
try it anyway: --SET QUOTED_IDENTIFIER Off will do the magic
----------------------start copy---------------------------
SET QUOTED_IDENTIFIER Off
GO

declare @str varchar(4000)
declare @var varchar(50)
set @var='master'
set @str="SELECT * FROM OPENQUERY(TEST,'SELECT * FROM master..sysfiles WHERE name =''"  + @var + "''')"
print @str
--SELECT * FROM OPENQUERY(TEST,'SELECT * FROM master..sysfiles WHERE name =''master''')
exec (@str)

------------------end copy-----
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question