January 5, 2007 at 1:44 pm
Hi,
I have sql server SQLBox1 defined as a linked server on sql server SQLBox2.
I have a data purge process (agent job running a proc) on SQLBox1 that deletes data older than 30 days from Table1 (on SQLBox1) on a periodic basis.
Also, I have a data migration process (agent job running a proc) on SQLBox2 that copies data from Table1 (on SQLBox1) to a table (on SQLBox2) using the linked server. Once done, it updates a flag in the migrated records on Table1 (on SQLBox1) to indicate they have been migrated. This is typically going to be more recent data received in the last 1 day or so.
These 2 processes run fine serially, but the moment I run them in parallel I get the following error message on SQLBox2:
OLE DB provider "SQLNCLI" for linked server "SQLBox1" returned message "Row handle referred to a deleted row or a row marked for deletion.".
The migration process is only migrating around 50 records at a time. The table Table1 (on SQLBox1) has around 80K-100K records.
Just wondering if anybody has ever seen this before. Any help would be appreciated.
Thanks,
Anil
January 5, 2007 at 3:54 pm
Looks like your update statement trying to update the row which marked marked for deleted.
If I am not mistaken SQL server will not delete physically when you run the delete command... rows will be marked as delete then later on they will be deleted...
Try with SET XACT_ABORT and DISTRIBUTED TRANSACTION
statements while updating/inserting the data using the linked server...
MohammedU
Microsoft SQL Server MVP
January 5, 2007 at 8:51 pm
No, the records being deleted are much older than the records being updated. So, the same record will never be deleted and updated.
I am already using SET XACT_ABORT and DISTRIBUTED TRANSACTION when updating thru linked server.
Thanks.
January 6, 2007 at 1:09 am
Are there any triggers on the table?
Run the profiler to see what is happenning behind the scenes...
MohammedU
Microsoft SQL Server MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply