0

I have been working on a trigger aimed to update a mirror table stored in Mysql server from a SQL Server instance.

The trigger is fired when insertions are made in a table of the SQL Server database and it tries to update the MySQL table which can be accessed using Linked-Servers mechanism throught the MySql ODBC driver.

The problem is that in SQL Server triggers are protected by a distributed transaction which is not supported the linked server:

The operation could not be performed because OLE DB provider "MSDASQL" for linked server "*****" was unable to begin a distributed transaction.

I have read that I could try to disable remote procedure transactions. For SQL Server 2005:

sp_configure 'remote proc trans', '0';
RECONFIGURE;

But I don't like the idea itself. Besides, it doesn't solve the problem.

Is there any way to enable remote transaction for ODBC linked servers? If not, how can I make it this configuration work?

Pablo Francisco Pérez Hidalgo
  • 27,044
  • 8
  • 36
  • 62
  • 1
    Well according to http://stackoverflow.com/questions/7204452/distributed-transactions-between-mysql-and-mssql it's supported by OLE DB and ODBC so the problem might be with MySQL. http://stackoverflow.com/questions/6669038/is-mysql-capable-of-distributed-transactions-off-the-box-like-sql-servers-b – ta.speot.is Aug 01 '13 at 10:37
  • @ta.speot.is It would be great to know if it is possible to configure a trigger not using distributed transaction – Pablo Francisco Pérez Hidalgo Aug 01 '13 at 13:22

0 Answers0