Scenario
Recently I was working in a project where I required data to be transferred from a remote server and do some manipulations with it. The status column in the remote server table have to be updated later. The remote server in this case was MySQL server and we were using ODBC DSNs based connectionstring.
When we try to execute an update statement against the remote server as below
UPDATE t
SET col1 = value1,
col2 = value2
FROM OPENQUERY(LINKEDSERVERNAME,’select col1,col2,col3 from mysqlserver.tablename’) t
WHERE col3 = value3
We got an error message as given below
OLE DB provider “MSDASQL” for linked server “MYLINKEDSERVER” 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 “MYLINKEDSERVER” 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.
Solution
We tried tweaking the update query but still was not able to overcome the error
I searched regarding the error and finally managed to get the solution from a forum.
The solution required tweaking a small setting in the ODBC DSN property as shown below
Once we set the Return matched rows instead of affected rows option the UPDATE query executed successfully.
This setting will allow the updating of values in MySQL to the fields which has a value already.
