|
|
|
Grasshopper
 
Group: General Forum Members
Last Login: Today @ 1:12 PM
Points: 22,
Visits: 109
|
|
I need to update a table in a MySQL linked server from SQL2K5. This worked the first time or two I ran it but now it gives me an error every time.
UPDATE openquery(MYLINKEDSERVER, 'Select Autonumber, BDD_Name FROM Table1 WHERE Autonumber = 40')
Set BDD_Name = 'AL10020'
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.
I have verified that the Autonumber column is the primary key for the table.
Any ideas on how to resolve this?
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, October 28, 2009 12:00 PM
Points: 1,
Visits: 89
|
|
I've run into the same thing, and it seems like the data is being held in cache (or something) and it won't update it because the column values have changed.
Example: if you are changing a field to 'updated' when it was 'pending' - it will work the first time but then if you try to change it to 'updated' when it's already been changed by your first attempt, it fails and tells you the row isn't the same as before. Perhaps it is checking each value from what it originally was before attempting to update?
I could not get around this nor did I find a solution with a thorough search, I set the value of the field back to 'pending' and then tried again - it worked so I believe it is an ODBC or MySQL behavior. Just know that you may not be able to update the same column more than once within the same process/transaction, and if so, you should do it in different connections would be my guess.
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, October 27, 2009 9:59 PM
Points: 5,
Visits: 54
|
|
I also have the same issue when trying to run an update query in a linked mysql server from SQL Server 2005.
The OLE DB provider "MSDASQL" for linked server "ZZZZZZ" 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.
It worked the first time but now consistently gives the error. I have tried dropping and re-creating the linked server before running the query, but this doesn't help.
I need to update the values in the mysql table based on data in SQL Server on a regular basis. Are there any other solutions?
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, October 28, 2009 1:54 PM
Points: 5,
Visits: 27
|
|
I have found the same behavior using SQL server 2000 and another post that I read seems to indicate that it is a specific behavior of MySQL that will not allow you to issue an update statement unless you actually change a value. I have tested this with our linked server and it would appear to be the case. I get the above error 100 percent of the time that i try to update a row with the same values that were in the row before the update. If I however change even one column value the update succeeds no problem.
As a result I have had to write handling to bail out of the update if the values in the row will remain unchanged.
Here is the post i was referring to.
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/266c5992-6f02-4cab-89d7-003b2fdfeed2
|
|
|
|
