Linked Servers - SQL Server Forums

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

SQL Server Development (2000)

Linked Servers

Author

Topic

snarayan
Starting Member

10 Posts

Posted - 2004-05-20 : 21:48:52

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

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-21 : 10:43:38

?? Try putting

BEGIN DISTRIBUTED TRANSACTION Tran1

query

COMMIT Tran1

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

snarayan
Starting Member

10 Posts

Posted - 2004-05-24 : 17:19:12

Did not work. Got the same error message. Have decided to use Openquery with dynamic SQL ie Openquery does not take variables.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-24 : 18:30:30

Your UPDATE statement isn't referring to the linked server name.

You've got this:
update Account.dbo.Accounting set Amount = 0, LastUpdate = getdate()
where USERNAME = 'xxx' and ACCTSTATUSTYPE = 'Alive'

Try this:

update Balance.Account.dbo.Accounting set Amount = 0, LastUpdate = getdate()
where USERNAME = 'xxx' and ACCTSTATUSTYPE = 'Alive'

Or this:

update a set Amount = 0, LastUpdate = getdate()
from Balance.Account.dbo.Accounting a
where USERNAME = 'xxx' and ACCTSTATUSTYPE = 'Alive'

Tara

hjartu
Starting Member

3 Posts

Posted - 2004-08-31 : 10:58:37

Was this issue ever resolved? I am having similar issue and I have a fully qualified name but similar error.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources