Linked Server OpenQuery | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Linked Server OpenQuery

Hi Have 2 SQl 2000 Linked Servers, Server A and B. Doing a table update on Server A from Server B using linked Servers. I can do a select select * from Balance.Account.dbo.ACCOUNTING
where USERNAME = ‘xxx’ and ACCTSTATUSTYPE = ‘Alive’ But when I do an update using TSQL update Account.dbo.Accounting set Amount = 0, LastUpdate = getdate()
where USERNAME = ‘xxx’ and ACCTSTATUSTYPE = ‘Alive’
It fails with the error msg Server: Msg 7346, Level 16, State 2, Line 1
Could not get the data of the row from the OLE DB provider ‘SQLOLEDB’.
[OLE/DB provider returned message: Row handle referred to a deleted row or a row marked for deletion.]
OLE DB error trace [OLE/DB Provider ‘SQLOLEDB’ IRowset::GetData returned 0x80040e23]. But update using openquery works update OPENQUERY(Balance, ‘Select * from Account.dbo.Accounting
where USERNAME = ”xxx” and
and ACCTSTATUSTYPE = ”Alive”’)
set Amount = 0, LastUpdate = getdate()
Can someone please explain why the TSQL fails but openquery works. Thanks
Sanjay
Openquery is the only way to query the linked server, refer to books online on Linked server architecture. The linked server name is to be specified in an OPENQUERY statement to open a rowset from the OLE DB data source. This rowset can then be referenced like a table in Transact-SQL statements. For infrequent references to a data source, the OPENROWSET or OPENDATASOURCE functions are specified with the information needed to connect to the linked server. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I have seen that happening before. The problem was that I updated the data structure of one of my tables on one iof the servers and my 4 part table name updates stopped working. I ended up using OPENQUERY. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
Without OPENQUERY no other go with linked servers and its ideal. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Will use Openquery . The thing I do notlike about openquery is that it does not take variables, have to build a dynamic query.

]]>