|
|
|
Grasshopper
 
Group: General Forum Members
Last Login: Wednesday, January 13, 2010 8:17 AM
Points: 22,
Visits: 113
|
|
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: Tuesday, December 15, 2009 5:35 PM
Points: 1,
Visits: 90
|
|
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: Thursday, December 10, 2009 5:00 PM
Points: 5,
Visits: 55
|
|
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, January 13, 2010 9:09 AM
Points: 5,
Visits: 30
|
|
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
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Sunday, January 17, 2010 9:21 PM
Points: 3,
Visits: 30
|
|
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/
|
|
|
|
