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.
| 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. |
 |
|
|
|
|
|
openrowset with variable to pass in