Can't enable non transacted updates

les28
les28 used Ask the Experts™
on
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)

Fails with:
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
http://msdn.microsoft.com/en-us/library/ee251875(BTS.10).aspx

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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> INSERT OPENQUERY([BM], 'SELECT * FROM Events')
values (2, '2010-01-02', 'Palace',1)

Instead of *, specify column names and give it a try..
Haven't tried out INSERT's myself earlier and hence it is a guess..

And FYI, few DBMS doesn't supports INSERT operations using OPENQUERY.

Author

Commented:
Thanks for the suggestion rrjegan17.

Unfortunately specifying the column names results in the same error message.

Are there any other solutions other than OPENQUERY?

I suppose I script the creation of a csv file, ftp it to the server and then import it into MySQL, but that seems a very long way around.
Commented:
I solved it.  For the record...
I found the error listed in a troubleshooting section of the MySQL manual  see here (http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-errors.html#qandaitem-20-1-7-3-1-3)

Which shows that the InnoDB engine must be used on the MySQL database.

I contacted my hosting company and requested that it be enabled.  After this was done, inserts work.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial