Distributed transactions between MySQL and MSSQL

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?

-------------Problems Reply------------

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...

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

On Windows XP and Windows Server 2003:

  1. From Control Panel, open Administrative Tools, and then open Component Services. You can also click the Start button, click Run, type dcomcnfg in the Open box, and then press OK to open Component Services.
  2. Expand Component Services, Computers and right-click My Computer, and then select Properties.
  3. Click the MSDTC tab, and then click Security Configuration.
  4. Select the Enable XA Transactions check box, and then click OK. This will cause a MS DTC service restart.
  5. Click OK again to close the Properties dialog box, and then close Component Services.
  6. Stop and then restart SQL Server to ensure that it syncs up with the MS DTC changes.

On Windows Vista and Windows Server 2008:

  1. Click the Start button, type dcomcnfg in the Start Search box, and then press ENTER to open Component Services. You can also type %windir%\system32\comexp.msc in the Start Search box to open Component Services.
  2. Expand Component Services, Computers, My Computer, and then Distributed Transaction Coordinator.
  3. Right-click Local DTC and then select Properties.
  4. Click the Security tab on the Local DTC Properties dialog box.
  5. Select the Enable XA Transactions check box, and then click OK. This will cause a MS DTC service restart.
  6. Click OK again to close the Properties dialog box, and then close Component Services.
  7. Stop and then restart SQL Server to make sure that it syncs up with the MS DTC changes.

See also Registry Entries Are Required for XA Transaction Support and validate that the third party driver XA DLL creates these registry entries.

Category:mysql Views:4 Time:2011-08-26

Related post

  • Why does opening a connection throw a distributed transactions error in MySQL? (.NET Connector) 2011-09-01

    I'm opening a connection to a local MySQL server and on the connection.Open() method it throws this error: System.NotSupportedException: MySQL Connector/Net does not currently support distributed transactions. at MySql.Data.MySqlClient.MySqlConnectio

  • table transfer from MySQL to MSSQL fails 2009-12-11

    I am trying to transfer a table from MySQL to MSSQL using the data migration tool in MS SQL Management studio. I am using .net provider for odbc and selecting the tabel and trying to tansfer. But is is getting the total tables in the database but whe

  • Are Distributed Transactions a good idea for enabling rollback of database upgrades in Windows Installer Custom Actions? 2010-08-09

    I've outgrown the Sql Server custom actions available in WiX, so I'm taking the bold step of creating my own using Deployment Tools Foundation. I want to be a good citizen and make sure that mine support rollback. But what's the best way of doing it?

  • Why isn't TransactionScope rolling back distributed transactions? 2010-10-17

    I am using an object persistence framework called ECO for updating data to SQL Server. I've noticed that if I create a TransactionScope and then deliberately throw an exception after my first transaction has committed but before my second has committ

  • Transaction Scope fails with BeginTransaction in Oracle : Connection is already part of a local or a distributed transaction 2011-07-29

    Having this strange behavior while using OracleConnection with TransactionScope. If i try to use connection.BeginTransaction() in a transaction scope i get simple elegant InvalidOperationException : Connection is already part of a local or a distribu

  • Java EE / EJB vs Spring for Distributed Transaction management with multiple DB Clusters 2012-01-25

    I have a requirement to produce a prototype (running in a J2EE compatible application server with MySQL) demonstrating the following Demonstrate ability to distribute a transaction over multiple database located at different sites globally (Applicati

  • TransactionScope and Distributed Transaction Coordinator on Oracle 2012-02-24

    I know how to implement a distributed transaction when executing queries across two MSSQL DBs using the DTC and the TransactionScope class. Are there infrastructure options available for Oracle on Windows and Oracle on *nix that provide the same serv

  • Sharding and Transactions with MySQL 2012-04-07

    With sharding, how can you maintain a reliable transaction across multiple database servers? For example, if I had a table named AccountLedger on one database server (MySQL instance) and a table named User on another database server, is it possible t

  • How do distributed transactions work (eg. MSDTC)? 2008-09-11

    I understand, in a fuzzy sort of way, how regular ACID transactions work. You perform some work on a database in such a way that the work is not confirmed until some kind of commit flag is set. The commit part is based on some underlying assumption (

  • How do I fix the error unable to enlist Sybase database in distributed transaction? 2008-10-02

    I know very little (arguably nothing) about Sybase setup, but I do know SSIS is having trouble enlisting Sybase in a distributed transaction. Has anyone been able to make this work? The SSIS Runtime has failed to enlist the OLE DB connection in a dis

  • distributed transaction between two MS SQL Server 2005 2008-10-16

    I need to make DISTRIBUTED TRANSACTION between two MS SQL Server 2005, it drops an error that NO ACTIVE TRANSACTION IS PRESENT or smth like this. DTCTESTER programs drops an error that transaction could not be done. btw 135 port is open on the firewa

  • XA distributed transactions in C++ 2009-01-27

    Is there a good C++ framework to implement XA distributed transactions? With the term "good" I mean usable, simple (doesn't imply "easy"), well-structured. Due to study reasons, at the moment I'm proceeding with a personal implementation, following X

  • SSIS - Data from MySql to MsSql some characters are? 2009-02-08

    I just transferred some data from MySql to MsSql (2K5) in a text field, some of my characters, such as apostrophes, are now ? (question mark) to me this indicates some sort of collation or character set error, right? To be honest, I don't know which

  • Check Contraint Bypassing CATCH block in Distributed Transaction 2009-02-13

    I have a MSSSQL stored procedure performing a distributed transaction that looks like this: SET XACT_ABORT ON; SET NOCOUNT ON; BEGIN TRY BEGIN DISTRIBUTED TRANSACTION insert into LNKSRV.INST.dbo.zz (id, val) values (1, 'a'); insert into LNKSRV.INST.d

  • Distributed transactions with WCF 2009-03-13

    Basic architecture: n physical boxes each hosting the same WCF service, sitting begind a load balancer. Each box hitting a single database infrastructure that does not support transactions - don't ask :( So, in the data access layer of my app I need

  • MSDTC (Distributed Transaction Coordinator) Service Stops Unexpectedly 2009-03-27

    I am working on a Windows Server 2008, the MSDTC (Distributed Transaction Coordinator) Service Stops Unexpectedly and I cannot restart it again. When I go to the event viewer this is the error message that I have got: "A critical error occurred in an

  • Distributed Transaction from .NET to DB2 2009-04-02

    I have a problem where in I have to call into the AS400 db2 to call a series of insert/update stored procedures from my .net and then update a few SQL2005 tables if everything in the AS400 db goes ahead fine. The driver I am using does not have distr

  • Distributed Transaction Coordinator 2009-04-28

    I am trying make a database transaction(inserting records) across multiple system. So, I decided to use System.Transaction Namespace in .net. I configured MSDTC on both system(But i dont know whether i configured correctly). My transaction has two in

  • MSDTC (Distributed Transaction Coordinator) slow first-time startup 2009-05-15

    I've noticed that very simple distributed transactions take a good amount of time (3-4s) to complete on a first run. But if the same transaction scope is called again, it's much faster (30ms). It seems like MSDTC sort of goes to sleep after an hour.

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.153 (s). 11 q(s)