openquery update and optimistic concurrency

Hi, I need to update a mySQL database through a linked server in SQL.

I can successfully add, delete, but struggle to update a row twice.

exec ('UPDATE OPENQUERY (SIBC, SELECT UID, value1, value2 FROM table1 WHERE UID= "SCEP"'')
SET value1= "hello" WHERE UID= "SCEP"')

The first time I run the update, it succeeds, but thereafter I get the following error message :

OLE DB provider 'MSDASQL' 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.

[OLE/DB provider returned message: Row cannot be located for updating. Some values may have been changed since it was last read.]

OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData returned 0x80040e38: The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.].

Any ideas ?

Thanks.

Here are some suggestions:
1. This might be a specific limitation in the ODBC driver and/or how it interacts with the OLE/DB for ODBC drivers Provider (MSDASQL). You might try recreating this on another type of back-end to see if it reproduces there.

2. If another process is updating values on the mysql database, you may very well have optimistic concurrency issues...

3. You could try using 4-part names instead of openquery:
update sibc.dbo.db.table1 set value1='hello' where uid='scep';

4. You could do a pass-through query, as you are really only running queries against this back-end and not passing any data from SQL Server.

Conor Cunningham

|||Hi Conor, thanks for your reply.

The only process updating the system is my application as it's on the development environment.

I have an unusual issue in that I can update a datetime field in mySQL only once I've provided it a value explicitly through a mySQL query analyser utility.

The other odd problem I have is that when I perform the update, it has to actually update a field otherwise it fails, thus if I try update a column TEMP1 with a value of 1, but it already contains a value of 1, it fails.

PS: the provider is a mySQL provider, which doesn't allow 4 part naming in SQL.

I've a feeling the issue could exist with the ODBC driver, but unfortunitely the mySQL and Microsoft communities do not seem to work together too nicely.

Thanks for your help.

Karlo
|||This looks unclear.

It doesen't make sense to me to update the results of a select query.
If this worked the first time, my guess is that the table in the database did not change, only the clients memory-representation of it, and this confused the driver at the second try.

Not sure if I'm on the right track, but you could try to send the update query directly to the linked server.