update sql server 2000 from sql server 2005 using linked server
-
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!
-
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.
-
try create unique index in your remote table... i did that and the problem never appears anymore.
-
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 ShawFor 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 -
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.
-
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.
-
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply