using local variable in remote queries; anyone...?

[egreenberg]

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.

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!

 

[malexanian]

Instead of using OPENROWSET, why not link the remote server? Then you can reference the remote db as if it were on your local instance. Check Books Online for info on linking servers.

 

[tlbroadbent]

The Books Online documentation for OpenRowset is clear. The query string must be a constant. Variables are not allowed. You can link the server as suggested by Malexaznian. This is also my recommendation. Here are two other options.

Option 1: Dynamic SQL

DECLARE @bank_id int, @sql varchar(400)
SET @bank_id = 1
Set @sql=
'SELECT a.* FROM OPENROWSET' +
'(''SQL[/b]OLEDB'',''xx.xx.xxx.xxx'';''username'';''password'',
''SELECT * FROM DBName.dbo.tblBanks WHERE bank_id =' +
str(@bank_id) + ''') As a'
Exec(@sql)

Option 2: Apply the criteria locally

DECLARE @bank_id int
SET @bank_id = 1
SELECT a.*
FROM OPENROWSET('SQL[/b]OLEDB','xx.xx.xxx.xxx';'username';'password',
'SELECT * FROM DBName.dbo.tblBanks') As a
WHERE a.bank_id = @bank_id Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.