I am trying to run an update query on a remote MySQL database and populate a table there with content from a local MS SQL database.
At this point I am trying to run a simple insert query to test the connection.
I have set up the OBDC connection and created a linked server. The following query returns results with no errors:
SELECT * FROM OPENQUERY([BM], 'SELECT * FROM Events')
The problem comes in inserting data
INSERT OPENQUERY([BM], 'SELECT * FROM Events')
values (2, '2010-01-02', 'Palace',1)
Msg 7390, Level 16, State 2, Line 1
The requested operation could not be performed because OLE DB provider "MSDASQL" for linked server "BM" does not support the required transaction interface.
I have ensured that MTDTC allows Outbound Transaction Manager Communication
I have read this comment http://www.connx.com/products/connx/Connx%208.8%20UserGuide/Linked_Servers.htm
and also referred to
They both have the instructions to "click the Provider Options... button. In the Enable column select the Non transacted updates check box"
I can't find a Provider Options button.
I am running SQL Server 2005 on Windows Server 2003. Do these instructions apply?
Thanks for your help