sql server - Distributed transactions between MySQL and MSSQL

Question

I've tried for nearly a week now to get distributed transactions working. I've some procedures on MSSQL which try to select data from MySQL. My need is to do this in one(!) transaction. At the time I've set up ODBC connection on MSSQL with Single-Tier MySQL driver from OpenLink, which states me, that XA transactions work successfully (there is a test button integrated after configuring ODBC connection). Then I've set up a linked server in MSSQL via MSDASQL to this ODBC connection, but when doing

begin distributed transaction
    select * from optin..lu_source_proc
    select * from openquery(optinxa, 'SELECT * FROM tbl_source_proc')
commit transaction

I get the error, that no further transaction could be started inside the actual transaction. (Der OLE DB-Anbieter "MSDASQL" für den Verbindungsserver "optinxa" hat die Meldung "Es k?nnen keine weiteren Transaktionen in dieser Sitzung gestartet werden." zurückgeben.)

Another test:

set transaction isolation level serializable
begin transaction
    select * from optin..lu_source_proc
    select * from openquery(optinxa, 'SELECT * FROM tbl_source_proc')
commit transaction

Results in Der OLE DB-Anbieter "MSDASQL" für den Verbindungsserver "optinxa" hat die Meldung "[OpenLink][ODBC][Driver]Driver does not support this function" zurückgeben. But why states the ODBC driver on configuring, that XA transactions do work?

Answer 1

There are two important aspects here --

1) It seems that OLE DB does have support for distributed transactions - so I would assume that the Microsoft OLE DB Provider for ODBC Data Sources should too...

I would also assume that if MSDASQL did support distributed transactions then it would handle that functionality directly rather than delegating it to the ODBC Driver...

Distributed transactions are turned on in the ODBC driver by calling --

SQLSetConnectAttr(SQL_ATTR_ENLIST_IN_DTC)

So, enabling OpenLink driver logging via the DSN creation dialogs should help determine whether SQLSetConnectAttr(SQL_ATTR_ENLIST_IN_DTC) is called.

2) We would need to see additional tracing in order to see the ODBC activity immediately leading up to the "Driver does not support this function" error...

You can log a support case with OpenLink Software to take this further...