SQL Server - calling a variable...in openquery..

Asked By Hassan

12-Jan-08 07:31 PM

I want to get a value from a sql server table and then use that value into
an openquery call to a Mysql database... something like before.. but it
doesnt work..

declare @testid int

select @testid= max(id) from table1

select * from openquery (mysqlhost,'select *  from mysqltable where id > ' +
@testid )

12-Jan-08 08:10 PM

You have to convert the int to a varchar before concatenating its value with
the rest of the string:

select * from openquery (mysqlhost, 'select *  from mysqltable where id > '
+ convert (varchar (20), @testid))

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

12-Jan-08 08:40 PM

Hassan

You've been around long enough now to  know better than to just say "it
doesn't work".
Did you get the wrong answer? Did you get an error message? If so, what did
it say?

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com

12-Jan-08 11:05 PM

It says

Incorrect syntax near '+'.

13-Jan-08 01:51 AM

Did you try converting the variable to a varchar as Sylvain suggested?

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com

13-Jan-08 02:25 AM

Hassan
DECLARE @SQLx VARCHAR(500)
DECLARE @var VARCHAR(20)
SET @var = 'abcd'
SET @SQLx = 'SELECT * FROM OPENQUERY(Server,
''EXEC pubs.dbo.sp2 ''''' + @var + ''''''')'
EXEC(@SQLx)

13-Jan-08 06:59 AM

Hassan (hassan@hotmail.com) writes:

OPENQUERY does not accept an expression for the query. It has to be a
constant. This is because, SQL Server actually attempts to compile a plan
for the complete query, which is kind of difficult if the remote
query is unknown. (Hey, Kalen, you should have been around long enough
to know this. :-)

This leads to dynamic SQL, and in an orgy of nested quotes which
easily can drive you crazy. Uri's post looks scary, and but that is
just the beginning.

If you don't want to join the query reesult with something else, EXEC AT
is easier to use:

EXEC('SELECT * FROM mysqltable WHERE id > ?', @testid) AT mysqlhost

The ? is a placeholder for a parameter.

If you need to use OPENQUERY, have a look at
http://www.sommarskog.se/dynamic_sql.html#OPENQUERY for some tips for
mitigating the quote craze.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

13-Jan-08 07:10 AM

Hey Erland

Looks scary in terms of SQL Injection or nested quotes ? :-)

13-Jan-08 09:32 AM

Uri Dimant (urid@iscar.co.il) writes:

I was mainly thinking of the nested quotes, but you are right that
SQL injection is a risk too, if the input comes from unknown sources.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

13-Jan-08 01:09 PM

Yeah, yeah... I've done very little work with linked servers so that data
was removed from cache memory long ago!
;-)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com

15-Jan-08 10:51 PM

Alternatively, this leads to implementing this functionality on the
client, which is typically much easier.