Populating declared fields from a linked server

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

General SQL Server Forums

New to SQL Server Programming

Populating declared fields from a linked server

Author

Topic

tleonard
Starting Member

13 Posts

Posted - 2008-09-23 : 10:50:23

This works.
Select @edit = SORDNO,
@cadd = SAVSADDR
from flp612
where status = 'P'

where flp612 is table in local database

I want to change flp612 to point to the table on the linked server
CCTRANS. FYI.. the linked server is an iSeries (AS400)

This is how I coded it, but get error msg when clicking <Check Syntax>.

Select * from openquery([cctrans],
'Select
@edit = SORDNO,
@cadd = SAVSADDR
from flp612 where STATUS = ''P''')

Error 7321: An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'.
SQL0104 - Token = was not valid. Valid tokens: + - AS <IDENTIFIER>.
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare returned 0x80040e14].

tl

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-23 : 11:05:48

Those variables are out of scope within the context of the SQL statement executed via openquery. You need to set those variable on the outside, within the scope of your T-SQL statement, like this:

Select @edit=edit, @cadd=cadd
from openquery([cctrans],
'Select SORDNO as edit, SAVSADDR as cadd from flp612 where STATUS = ''P''')

- Jeff
http://weblogs.sqlteam.com/JeffS