OpenQuery using a variable

Hi,

Here's what I did:

1) I declared a new VARCHAR(2000) variable called CQUERY like this:
DECLARE @.CQUERY VARCHAR(2000)
2) I put a string query in the variable:
SET @.CQUERY = 'SELECT ...'

Now, when I try to execute the OpenQuery method using that variable, it fails.

Here's the call:
SELECT * FROM OPENQUERY(OracleSource, @.CQUERY)

I get the following error:
Server: Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near '@.CQUERY'.

Don't tell me I can't use a variable instead of a static query? What am I doing wrong?

Thanks,

Skip.i don't think you can do that, putting a variable in the from clause

you'll have to use dynamic sql

so put that statement in a EXEC(.....)|||Alright,

I tried it but I'm still having troubles with it. Here's my code (simplified version):

DECLARE @.CQUERY
SET @.CQUERY = 'SELECT * FROM OPENQUERY(OracleSource, ' + '''' + 'SELECT * FROM mytable WHERE last_name = ' + '''' + 'DOE' + '''' + '''' + ')'
EXECUTE(@.CQUERY)

When parsing, it's fine but at execution, it fails which is normal because it tries to execute the following query:

SELECT * FROM OPENQUERY(OracleSource, 'SELECT * FROM mytable WHERE last_name = 'DOE'')

It's, of course, incorrect because the query string stops before DOE because there's an apostrophy there so the system tries to execute the following query:

SELECT * FROM mytable WHERE last_name =

which is incorrect.

Any other suggestions?

Thanks again,

Skip.|||sorry if i mislead you the first time, what i meant is use EXEC if you are going to use a variable for openquery.

if you are not using a variable for openquery, then just do this:
SELECT * FROM OPENQUERY(OracleSource, 'SELECT * FROM mytable WHERE last_name = ''DOE''')|||Originally posted by Skippy_sc
Alright,

I tried it but I'm still having troubles with it. Here's my code (simplified version):

DECLARE @.CQUERY
SET @.CQUERY = 'SELECT * FROM OPENQUERY(OracleSource, ' + '''' + 'SELECT * FROM mytable WHERE last_name = ' + '''' + 'DOE' + '''' + '''' + ')'
EXECUTE(@.CQUERY)

When parsing, it's fine but at execution, it fails which is normal because it tries to execute the following query:

SELECT * FROM OPENQUERY(OracleSource, 'SELECT * FROM mytable WHERE last_name = 'DOE'')

It's, of course, incorrect because the query string stops before DOE because there's an apostrophy there so the system tries to execute the following query:

SELECT * FROM mytable WHERE last_name =

which is incorrect.

Any other suggestions?

Thanks again,

Skip.

Try this for instance:

DECLARE @.CQUERY varchar(8000)
SET @.CQUERY = 'SELECT * FROM OPENQUERY(MSSQL20,
''SELECT top 10 * FROM master.dbo.sysobjects where name=''+'sysobjects'+'')'
select @.CQUERY
EXECUTE(@.CQUERY)|||Thank you very much fattyacid, it works fine now!

Skipsql