Update MySql from MS SQL server
I need help in writing queries which will update MySql tables from SQL server. I have created linked server and select queries work fine but I'm getting errors while doing update. I'm really new to writing such type of queries so please help me understanding error message and what it means.
My update query:
UPDATE openquery(stagedb_za, 'Select acm_flag FROM aol_center WHERE nid = 6439')
Set acm_flag = 'P'
Error:
OLE DB provider "MSDASQL" for linked server "stagedb_za" 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 could not UPDATE table "[MSDASQL]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.
Answer
This whole issue goes away with a setting on the MySQL ODBC connector.
On the SQL Server that is linking to MySQL, go into the configure screen on the MySQL ODBC connection that is used by the linked server. Open "Details". On the "cursor/results" tab. Place a check in the "Return matched rows instead of affected rows".
Upon making that configuration change, updates that set a MySQL field value to the value it already has will not return an error.
Before I discovered this on another forum, I had put a LOT of code in to filter out the offending values on a field by field basis. This is easy.