I'm trying to update a single row using linked server from MS SQL 2012 to MySQL DB.

I run this query:

UPDATE b2b...product set [index]=`test_value` where id=12345;

Error Message:

The OLE DB provider "MSDASQL" for linked server "b2b" could not UPDATE table "[b2b]...[product]". 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 have googled this error and everyone recommends to check option "Return matched rows instead of affected rows" in MySQL ODBC Data Source Configuration, so I tried to do that, but it was already checked. So I tried to uncheck this, it didn't help, so I re-checked this setting.

If checking this option doesn't help, what else could I do?

By the way I tried the same update using MySQL WorkBench and it works fine.

Edit: this is very weird. When I update a particular row using MySQL WorkBench then I'm able to update it using SQL Server Management Studio. I tried it on several rows. BTW, I had to set safe mode in MySQL WorkBench in order to be able to update rows.

  • Does EXEC('UPDATE product set [index]=''test_value'' where id=12345;') AT b2b work or does it still fail? Oct 12 '16 at 8:56
  • Wow, this worked. Thank you for help. BTW can i mark it as answer if it's a comment?
    – Konrad
    Oct 12 '16 at 9:22

You can use

EXEC('UPDATE product set [index]=''test_value'' where id=12345;') AT b2b

as an alternative syntax, which executes the query in pass-through mode.

The reason why you're getting the optimistic locking error is probably because the query can't be run in pass-through, so SQL Server downloads the whole table, locates the row to update and then pushes the row back to the linked server. Between the moment the row is read and the moment the row is written, the value has changed in the linked server, so you have an optimistic locking violation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.