I have setup a MYSQL server as a linked server in SQL SERVER 2000. I am able to select from a table using OPENQUERY as I understand that you can't use the 4 part qualifier to access an object from MYSQL as the ODBC driver does not expose the proper interface for this. The problem is that I cannot update,delete or insert into the OPENQUERY() table
UPDATE OPENQUERY(MySQL_LinkedServer,'select id from msqlTableTest') SET id = 0
INSERT INTO OPENQUERY(MySQL_LinkedServer,'select id from msqlTableTest') VALUES(0)
DELETE FROM OPENQUERY(MySQL_LinkedServer,'select id from msqlTableTest') WHERE id = 0
This is the OLEDB PROVIDER FOR ODBC error I am getting...
The requested operation could not be performed because the OLE DB provider 'MSDASQL' does not support the required transaction interface.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface returned 0x80004002].
I know there are brilliant Professionals on this forum. Please tell me that this is just an MDAC upgrade or something that is not configured correctly...
Linked server query must be supported and contained a pass-thru query (OpenQuery, or OpenRowset with query a inside it).
SELECT/INSERT/UPDATE/DELETE statement against a 4-part name for which SQL Server generates a query against the provider for optimization.
Thanks for the article reference. I had a look at it and it is definitely a different error that is explained in it. As I said above, you can't use 4 part names to access objects in mysql as the odbc driver does not allow it. Does anyone at all know why this error would be causes as MS does not document the reasons for it. Remember, I can select from the openquery but not update,insert,delete...