February 16, 2009 at 10:09 am
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?
June 17, 2009 at 12:32 pm
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.
August 10, 2009 at 6:07 pm
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?
October 28, 2009 at 11:44 am
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.
January 12, 2010 at 8:31 pm
I had the same problem using SQL 2005 with a linked MySQL database.
After a few tests it was definately caused by trying to update a column in the MYSQL table to the value that was already there.
Here is the initial code :
UPDATE p
SET barcode = N.barcode
FROM MySQL_RSS...products p
JOIN RSS_ProductNew N
ON p.id = N.id
WHERE N.NewRecord = 1
AND N.Invalid = 0
All this is doing is updating the barcode field in the products table of the linked MySQL database from the RSS_ProductNew table in the SQL Server database. The first time I ran, it worked fine, then I kep getting the error. I solved this by simply adding the following, so it only updates if it has actually changed :
AND p.barcode <> N.barcode
----------------------------------------------------------------------
Oh, and it took me a little while to be able to use this linked style rather than OPENQUERY, as I kept getting errors. The solution was to configure the MSDASQL provider using the following :
EXEC master.dbo.sp_MSset_oledb_prop N'MSDASQL', N'AllowInProcess', 1
EXEC master.dbo.sp_MSset_oledb_prop N'MSDASQL', N'IndexAsAccessPath', 1
EXEC master.dbo.sp_MSset_oledb_prop N'MSDASQL', N'LevelZeroOnly', 1
EXEC master.dbo.sp_MSset_oledb_prop N'MSDASQL', N'DynamicParameters', 1
EXEC master.dbo.sp_MSset_oledb_prop N'MSDASQL', N'NestedQueries',
I had to do it this way rather than using the Management Studio since I am using SQL Express which does not have the 'Properties' option for configuring providers.
This article is a great run through for setting up a linked MySQL server
http://www.ideaexcursion.com/2009/02/25/howto-setup-sql-server-linked-server-to-mysql/
February 26, 2010 at 7:56 am
thank you for your provider options!
instead to remove optimistic update problem:
configure mysql-odbc to return matched rows instead of affected rows.
in mysql-odbc control panel Flags1, first option. 😀
August 30, 2010 at 4:29 pm
UPDATE OPENQUERY(MYSQL_LEAN,'SELECT DB_Status,Id FROM DBSyncStatus where Id=1 ') SET DB_Status =2678
I too had the same problem with issuing linked query to MYsql db from MSSql 2000.
What I did is Id is a primark key for my table MYSQL . Please add Id (primary key ) in the select list while querring .Problem will get resolved
July 21, 2012 at 8:43 am
Adding primary key works perfectly
thanks ragha.koduri1986
May 10, 2018 at 5:06 pm
I had the same issue when updating to an Oracle linked server: "ROW-00014: Cannot update row as the data in the database has changed". If you do an update on a set but none of the data actually changes, it generates this error
I was able to get round it be doing a test on the data before the update to make sure at least one of the field was different
select wp.person_id, wp.surname , wp.firstname, ...
into #temp_delta
from #temp_wods_person wp
join #temp_repo_person rp on (rp.person_id = wp.person_id)
where rp.surname <> wp.surname
or p.firstname <> wp.firstname
...
set @delta_count = (select count(*) from #temp_delta)
if @delta_count <> 0 then update...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply