June 16, 2005 at 9:27 am
Hi,
I get the following error message when I try to update data on a remote server. My query is
UPDATE ProductionDB.Catalog.Dbo.Product
SET Description = remoteProduct.Description,
SequenceNo = remoteProduct.SequenceNo ,
Side = remoteProduct.Side ,
Price = remoteProduct.Price,
UpdatedDate = remoteProduct.UpdatedDate
FROM ProductionDB.Catalog.Dbo.Product remoteProduct
INNER JOIN CentralDB.dbo.Product as ProductMaster
on ProductMaster.ProductID = remoteProduct.ProductID
WHERE ProducTMaster.UpdatedDate > @LastRunDate
When @LastRunDate is 3-4 days in the past and causes a lot of updates I get the following error message.
Server: Msg 7343, Level 16, State 4, Procedure tester, Line 16
OLE DB provider 'SQLOLEDB' could not UPDATE table '[ProductionDB].[Catalog].[Dbo].[Product]'. The consumer could not delete the row. A deletion is pending or has already been transmitted to the data source.
[OLE/DB provider returned message: Cursor operation conflict]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IRowsetChange::SetData returned 0x80040e23: The consumer could not delete the row. A deletion is pending or has already been transmitted to the data source.].
Any Ideas
Cheers
A
June 16, 2005 at 9:32 am
Do you have a distributed transaction opened?
June 16, 2005 at 9:39 am
Yes. I moved this script to a test SP as follows
CREATE PROCEDURE tester
(
@LastRunDate DateTime
)
AS
SET XACT_ABORT ON
begin distributed transaction
update ProductionDB.......
Commit etc....
I search the web but found nothing.
June 16, 2005 at 9:56 am
Just in case you still have a transaction opened :
Rollback distributed tran
June 16, 2005 at 10:08 am
I give up. I think I will go back to the old way of comparing the updatedDate field on the central DB with the remote DB and update where they differ. While this takes longer to do as all rows returned from the remote server even for 1 update. It doesnt cause any errors and it will have to do for now as I cant spend anymore time on it.
June 16, 2005 at 10:20 am
Had to do the same here with a linked server... Optimized it as much as I could, but I'm stuck at 20 secs. But since it's used only 2-3 times week, it's not dramatic.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply