CODEWITHSUNDEEP
mysqlsql-server-2008linked-serveropenquery
pramodtech
pramodtech

Reputation: 6270

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.

Upvotes: 5

Views: 9683

Answers (2)

Tom Shindler
Tom Shindler

Reputation: 171

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.

Upvotes: 17

pramodtech
pramodtech

Reputation: 6270

Ok, I got the answer. It seems like when your are updating column value and if new value is same as existing one then it is treated as there is no change so no updation will happen.

In my case I was updating acm_flag to 'P' but it's value was already 'P' so no updation happened. When I tried to update it to different value it worked just fine.

Upvotes: 5

Related Questions