using locally declared variables in remote query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

using locally declared variables in remote query

Author

Topic

egreenberg
Starting Member

2 Posts

Posted - 2002-09-25 : 16:33:39

I am writing a stored proc. that will copy data from tables in a remote location, to nearly identical tables in my local db. I need to pass a variable that is declared and set on my local db, into the query that selects data from the remote db. I am doing these queries throught OPENROWSET. (the copy procedure is very specific in what it needs - I cannot make use of data migration tools for this)

example:

DECLARE @bank_id int
SET @bank_id = 1
SELECT a.*
FROM OPENROWSET('SQLOLEDB','xx.xx.xxx.xxx';'username';'password','SELECT * FROM DBName.dbo.tblBanks WHERE bank_id ='@bank_id)
AS a

the problem is that the parser doesn't recognize the @bank_id when it is used in the OPENROWSET(), presumably because it is declared and set on my local db, and I am trying to use it in a remote query. I've tried several variations of the above example.

Question: how can I get the value of the locally declared variable into the remote query? I already tried to put the whole SELECT statement into a string variable, concatenate with @bank_id, and then use that string variable in the remote query but to no avail.

If anyone can help, much appreciation and many good thoughts will be sent your way.

Thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-25 : 16:41:11

You can do this if you make the entire OPENQUERY statement dynamic SQL:

DECLARE @bank_id int, @sql varchar(8000)
SET @bank_id = 1
SET @sql='SELECT *
FROM OPENROWSET(''SQLOLEDB'',''xx.xx.xxx.xxx'';''username'';''password'',''SELECT * FROM DBName.dbo.tblBanks WHERE bank_id =' + CAST @bank_id AS varchar + ') '
EXEC (@sql)

This will work, but you might have to tweak the single quotes somewhat to get the proper doubling.

egreenberg
Starting Member

2 Posts

Posted - 2002-09-25 : 17:06:12

Thanks! With a little syntactical tweaking, it works great. I appreciate the help.

Eric

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources