openquery

BOL says that 'OPENQUERY does not accept variables for its
arguments'. So, I tried using something like:
Declare @.qry varchar(1000)
Set @.qry = 'select * from OPENQUERY
(<linked_server_name>, ''<query>'')'
exec (@.qry)
Would this work? Thanks.Yes.
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:018301c3ad3f$50936e10$a401280a@.phx.gbl...
> BOL says that 'OPENQUERY does not accept variables for its
> arguments'. So, I tried using something like:
> Declare @.qry varchar(1000)
> Set @.qry = 'select * from OPENQUERY
> (<linked_server_name>, ''<query>'')'
> exec (@.qry)
> Would this work? Thanks.|||That is called Dynamic SQL and should work if the final syntax comes out
correct.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:018301c3ad3f$50936e10$a401280a@.phx.gbl...
> BOL says that 'OPENQUERY does not accept variables for its
> arguments'. So, I tried using something like:
> Declare @.qry varchar(1000)
> Set @.qry = 'select * from OPENQUERY
> (<linked_server_name>, ''<query>'')'
> exec (@.qry)
> Would this work? Thanks.

Opendatasource

I was able to use the following select qry with an OpenDataSource, but I
recieve and error when I change it to an Update qry.
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\Posiden\apps\Access\MsData\holdbk.mdb";
User ID=Admin;Password='
)...[HoldOrderDetails_View] h,BoxIdCount b
Where h.[Line#]= b.BoxId and b.Task = 1
but when I change it to and Update it errors out. See code below
update h
set h.LOCATION = b.Location
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\Posiden\apps\Access\MsData\holdbk.mdb";
User ID=Admin;Password='
)...[HoldOrderDetails_View] h,BoxIdCount b
Where h.[Line#]= b.BoxId and b.Task = 1
I get the following error; Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'h'.
Any suggestions.
Thank you in advance for your helpdouble post by mistake?
"bhorwitz" <bhorwitz@.discussions.microsoft.com> wrote in message
news:33E09107-36E6-4131-A5F8-7D1053521CD3@.microsoft.com...
> I was able to use the following select qry with an OpenDataSource, but I
> recieve and error when I change it to an Update qry.
> SELECT *
> FROM OPENDATASOURCE(
> 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="\\Posiden\apps\Access\MsData\holdbk.mdb";
> User ID=Admin;Password='
> )...[HoldOrderDetails_View] h,BoxIdCount b
> Where h.[Line#]= b.BoxId and b.Task = 1
> but when I change it to and Update it errors out. See code below
> update h
> set h.LOCATION = b.Location
> FROM OPENDATASOURCE(
> 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="\\Posiden\apps\Access\MsData\holdbk.mdb";
> User ID=Admin;Password='
> )...[HoldOrderDetails_View] h,BoxIdCount b
> Where h.[Line#]= b.BoxId and b.Task = 1
> I get the following error; Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'h'.
> Any suggestions.
> Thank you in advance for your help|||try this..
untested..
update h
set LOCATION = b.Location
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\Posiden\apps\Access\MsData\holdbk.mdb";
User ID=Admin;Password='
)...[HoldOrderDetails_View] h,BoxIdCount b
Where h.[Line#]= b.BoxId and b.Task = 1