?
Solved

Enclose variable in OPENQUERY SELECT clause

Posted on 2005-03-02
10
Medium Priority
?
795 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Independent Software Vendors: 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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

762 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