0

When I try to update my customer table that is on linked server, from my procedure on mssql server, I get this error: 

 OLE DB provider "MSDASQL" for linked server "PRESTA" returned message "Row cannot be located for   updating. Some values may have been changed since it was last read.".
 Msg 7343, Level 16, State 4, Line 1
 The OLE DB provider "MSDASQL" for linked server "PRESTA" could not UPDATE table "[PRESTA]..  [prs_customer]". The rowset was using optimistic concurrency and the value of a column has been changed     after the containing row was last fetched or resynchronized.

I am 100% sure that the value I am trying to put in the birthday column of my Customer table is not the same as the value that is already in it:

UPDATE PRESTA...prs_customer

SET birthday = @birthday
WHERE id_customer = @id_customer

This error is showing only because the value already in birthday column is: "0000-00-00", and not null. When I change directly in database to real NULL, my procedure works, and it doesn't give any errors.

Also, both values are of type DATE, have the same format, so that is not a problem.

I am baffled by this, so if anyone please elaborate?

msrd0
  • 7,816
  • 9
  • 47
  • 82
Admir Huric
  • 137
  • 8

1 Answers1

1

Just read the error message. The link server is using optimistic concurrency. Thus, a record is read via a cursor in the OLE DB layer. When you try to perform the update, the data has changed. Optimistic means use locking just before you do an update.

Are you sure this is the exact SQL statement?
Are there any other processes that might change the id during your update?

Check out the ADO book from MS Press.

http://web.archive.org/web/20021222065228/http://www.microsoft.com/mspress/books/sampchap/3445.asp

It shows the same error you are experiencing.

Unless it is a bug in the OLE DB provider or MySQL, it looks to me as a locking issue.

Can you increase the locking level to pessimistic? If you do, you will have to handle blocking in your code.

I would also run a trace in MySQL to see what statements are hitting the DB Engine.

Sincerely

John

CRAFTY DBA
  • 14,351
  • 4
  • 26
  • 30