Update MySQL from MS SQL Server 2008

Question

I have in Microsoft SQL Server 2008 R2 SP1 a Connect-Server with ODBC to a MySQL database. ODBC-Driver: MySQL ODBC 5.2a on Microsoft Windows Server 2008 R2 64bit

All queries actually run properly. Also Inserts and Updates.

The problem is an update with BLOB-data. (images, ...)

Initially it worked, but when there is more data were not. Then I limited the update statement.

Now it doesn't even work with one image.

The table has 2540 datasets. 142 datasets have BLOB-Field = NULL

MySQL TABLE:

-ID int not null Primary Key
-ARTIKEL int 
-BILD blob

MySQL ODBC

Parameter MSDAQL enter image description here

Parameter Verbindungsserver (in MSSQL) enter image description here

This ist the SQL query:

update shop...EXTERN_BILDER 
set BILD = (select b.BILD from INTERN_BILDER b where b.ID = EXTERN_BILDER.ID)
where (BILD is null) AND (ID in (select ID from _temp_ID))

table _temp_ID has the IDs to update. I can adjust, how many.

This is the error from MSDASQL for connected server shop (translated from german, as best as I could):

The rows to be updated were not found. Some values were modified since last read.

Message 7343, Level 16, Status 4, Row 2 The OLE DB-Provider 'MSDASQL' for the connection server 'shop' couldn't execute UPDATE for the [shop]...[extern_pictures] table. For the rowset complete parallelism was used and the value of a row has been modified, after the corresponding row was last read or synchronized.

YvesR YvesR · Accepted Answer · 2013-06-15T17:38:34

it seems you try to update a row in a record that which changed while you request the rows. Try change your ODBC connection.

See => http://dev.mysql.com/doc/refman/5.5/en/connector-odbc-errors.html

Write Conflicts or Row Location Errors

How do I handle Write Conflicts or Row Location errors?

If you see the following errors, select the Return Matching Rows option in the DSN configuration dialog, or specify OPTION=2, as the connection parameter:

Write Conflict. Another user has changed your data.

Row cannot be located for updating. Some values may have been changed
since it was last read.

German: Hallo, es scheint als würdest Du Datensätze aktualisieren wollen, welche sich in der Zwischenzeit geändert haben. Du musst die ODBC Verbindung entsprechend einstellen, dass Du das UPDATE trotzdem ausführen kannst. Schaue Dir den Link an, dort steht, wie Du die ODBC Verbindung einstellen musst.

Update MySQL from MS SQL Server 2008

2 Answers