update sql server 2000 from sql server 2005 using linked server

Question

update sql server 2000 from sql server 2005 using linked server

Julia Esteban

Right there with Babe

Points: 753
More actions
June 5, 2007 at 11:49 am

#196265

Hello everybody,
When I do the next update in a sql server 2005 trying to update a table in an sql server 2000 using a linked server (vsql5) I always have the same result.
The query is:
UPDATE VSQL5.PHZED.DBO.SUBSCRIPCION
SET ITEMS = ITEMS + 1 ---
FROM VSQL5.PHZED.DBO.SUBSCRIPCION C , TEMP_SUB T
WHERE C.COD_SUBSCRIPCION = T.COD_SUBSCRIPCION
AND T.COD_PRODUCTO IN (1,2)
An I always have the error:
Message
Executed as user: SMS2\Administrator. Cannot get the data of the row from the OLE DB provider "SQLNCLI" for linked server "VSQL5". [SQLSTATE 42000] (Error 7346) OLE DB provider "SQLNCLI" for linked server "VSQL5" returned message "Row handle referred to a deleted row or a row marked for deletion.".

Can anyone help me or give me a hint?
Thanks in advance!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

diogenes1331 Default port Points: 1420 More actions · Answer 1

I am having similar problem. I am updating 2000 from 2005 and I get the following error

OLE DB provider "SQLNCLI" for linked server "ServerName" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Msg 16955, Level 16, State 2, Line 1

Could not create an acceptable cursor.

kiasu81 SSC Rookie Points: 41 More actions · Answer 2

try create unique index in your remote table... i did that and the problem never appears anymore.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Julia, try a slightly different syntax:

UPDATE C

SET ITEMS = ITEMS + 1 ---

FROM VSQL5.PHZED.DBO.SUBSCRIPCION C

INNER JOIN TEMP_SUB T

ON C.COD_SUBSCRIPCION = T.COD_SUBSCRIPCION

AND T.COD_PRODUCTO IN (1,2)

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sparky-407434 Right there with Babe Points: 731 More actions · Answer 4

Had same proble updating across a linked server.

A unique index did not work for me. I got it to work by:

Creating a temporary table in remote db then did a

INSERT INTO [tempremotetable]

select ... from [localtable]

Then update the remote table from the tempremotetable.

David-G Old Hand Points: 354 More actions · Answer 5

I have the same problem. I have a scheduled job on a SQL Server 2005 that deletes some data on a linked server which is SQL Server 2000.

The weird part is the job only fails 5% of the time, most of the time the job runs fine.

Scot Hurd-471796 SSC Enthusiast Points: 116 More actions · Answer 6

I had the same problem and worked around it by wrapping the DML statement in a proc on the remote machine then just calling the proc from the 2005 box.