openrowset with variable to pass in

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 2008 Forums

Transact-SQL (2008)

openrowset with variable to pass in

Author

Topic

elodea
Starting Member

5 Posts

Posted - 2015-02-09 : 03:20:15

Hi,

i tried to use openrowset to query a table from another server.
Tried to run query as below

Declare @operation int
Declare @trans_code1 int
Declare @trans_code2 int
Declare @trans_code3 int
Declare @from_date int
Declare @to_date int
Declare @facility varchar(max)
Declare @sqlCommand varchar(max)

set @operation = 4055
set @trans_code1 = 010617
set @trans_code2 = 060117
set @trans_code3 = 010701
set @from_date = 2014-08-01
set @to_date = 2015-01-31
set @facility = 'EM8888'

select a.*
FROM OPENROWSET('SQLOLEDB','servername';'username';'password',
'select distinct lot_id,a.quantity QtyIn,(quantity - reject_qty) as QtyOut,
cast(round(100.0 * (quantity - reject_qty)/quantity,1)as decimal(12,1)) as yield ,remarks as ITR
from EPCData.dbo.tepc_lot_history_ha a
where a.operation =@operation
and transaction_code in (@trans_code1,@trans_code2,@trans_code2)
and facility =@facility
and transaction_datetime between convert(datetime, @from_date) and convert(datetime, @to_date )
order by lot_id desc') AS a

and i got below error. What is the correct way to declare the variable?

Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@operation".

Thanks.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-09 : 09:00:30

Think about what you are doing here. You pass a string containing a query to a remote server. The remote server parses your string and hits the variable @operation. How would the remote server resolve that?

Basically you need to build up the query, substituting the variables, in the calling query and pass the fully-resolved string as the query to the remote server.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources