SQL Servant


I have an openquery statement with a parameter embeded as a variable:

declare @product varchar(3)

set @product= 'ABC'

select * from openquery(SomeServer,'

SELECT Description, Size

FROM Products

WHERE

Group = ''XY'' AND

Code = ''' + @product + '''')

When I run it I get the following message:

Msg 102, Level 15, State 1, Line 8

Incorrect syntax near '+'.

When I hard code the "Code" value, like so:

Code = ''ABC''')

...it works fine.

I am at a loss and would appreciate any help on this.

Thanks in advance

SQL Servant




Re: OpenQuery Syntax Using Variables

Andrew Sears - T4G


Can you try SET QUOTED_IDENTIFIER OFF

cheers,

Andrew







Re: OpenQuery Syntax Using Variables

Jerry Hung

I copied your code into Query Analyzer, same error too

It must be the quotation, do you want " or ' enclosing your @product

e.g. you want "Drink", or 'Drink'

This is my code for some script that uses OpenQuery, I think I had to use EXEC to run it for the same problem you had (it won't take +)

Code Snippet
EXEC ('SELECT * FROM OPENQUERY(SERVER, ''SELECT * FROM TABLE WHERE Table_Id = ' + @Table_Id_Str + ''')')







Re: OpenQuery Syntax Using Variables

SQL Servant

Tried it... same error.

Thanks,

SQL Servant

Reply to -----------------------------------------------------

Can you try SET QUOTED_IDENTIFIER OFF

cheers,

Andrew





Re: OpenQuery Syntax Using Variables

SQL Servant

The query sent to the server needs to have 'ABC' rather than "ABC".

I have used EXEC before and got the same error. Anyway, this query is part of an IF structure...

IF EXISTS (select * from openquery(...))

[do this ] ELSE [do that]

Thanks,

SQL Servant

Reply to ----------------------------------------------------------------

I copied your code into Query Analyzer, same error too

It must be the quotation, do you want " or ' enclosing your @product

e.g. you want "Drink", or 'Drink'

This is my code for some script that uses OpenQuery, I think I had to use EXEC to run it for the same problem you had (it won't take +)





Re: OpenQuery Syntax Using Variables

SQL Servant

I have fixed the problem...

Apparently it is to do with scope and stuff...

If I put the query in a EXEC command then it works. So, the code becomes this:

EXEC('

select * from openquery(SomeServer,''

SELECT Description, Size

FROM Products

WHERE

Group = ''''XY'''' AND

Code = ''''' + @product + ''''''')

')

In fact, I have put the entire IF structure (that this query is a part of) inside an EXEC command.

Over and out,

SQL Servant





Re: OpenQuery Syntax Using Variables

Jerry Hung

Wasn't that the idea of what I posted ;-P

I think I deserve a star, hee hee

Anyway, please mark this thread as Answered

Glad it worked out for you