Distributed Transactions Between MySQL and MSSQL

Question

I have been trying for almost a week to work with distributed transactions. I have some procedures in MSSQL that try to select data from MySQL. I need to do this in one (!) Transaction. At the time, I established an ODBC connection on MSSQL with the One-Tier MySQL driver from OpenLink, which tells me that XA transactions work successfully (there is a built-in test button after setting up an ODBC connection). Then I installed the linked server in MSSQL via MSDASQL with this ODBC connection, but when executed

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

I get an error that no further transaction can 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 is the ODBC driver specified when configuring that XA transactions work?

Answer 1

There are two important aspects to this:

1) It seems that OLE DB has support for distributed transactions - so I would suggest that the OLE DB provider for Microsoft ODBC data sources also ...

I would also suggest that if MSDASQL supports distributed transactions, then it will process this function directly, and not delegate it to the ODBC driver ...

Distributed transactions are included in the ODBC driver by calling -

SQLSetConnectAttr (SQL_ATTR_ENLIST_IN_DTC)

Thus, enabling OpenLink logging through DSN creation dialogs should help determine if SQLSetConnectAttr (SQL_ATTR_ENLIST_IN_DTC) has been called.

2) We will need to see additional tracing in order to immediately see the ODBC activity leading to the error "The driver does not support this function" ...

You can register a support file with OpenLink Software to accept this further ...

Answer 2

The following steps are necessary if you want to use XA data sources with Microsoft Distributed Transaction Coordinator (MS DTC) to process distributed transactions :

In Windows XP and Windows Server 2003:

• In Control Panel, open Administrative Tools, and then open Component Services. You can also click the Start button, Run, type dcomcnfg in the Open field, and then click OK to open the Services component.

• Expand Component Services, Computers, and right-click My Computer, and then select Properties.

• Go to the MSDTC tab and select Security Configuration.

• Select the Enable XA Transactions check box, and then click OK. This will restart the MS DTC service.

• Click OK again to close the Properties dialog box, and then close Component Services.

• Stop and restart SQL Server to make sure that it is synchronized with the changing MS DTC code.

In Windows Vista and Windows Server 2008:

• Click the Start button, type dcomcnfg in the Start Search box, and then press ENTER to open Component Services. You can also enter% windir% \ system32 \ comexp.msc in the Start Search field to open the Services component.

• Expand Component Services, Computers, My Computer, and then Distributed Transaction Coordinator.

• Right-click the local trouble code and select Properties.

• Click the Security tab in the Local DTC Properties dialog box.

• Select the Enable XA Transactions check box, and then click OK. This will restart the MS DTC service.

• Click OK again to close the Properties dialog box, and then close Component Services.

• Stop and restart SQL Server to ensure that it is synchronized with MS DTC changes.

See also Registry entries that are required to support XA transactions and confirm that a third-party XA DLL driver creates these registry entries.