XPost: microsoft.public.sqlserver.programming   
   From: esquel@sommarskog.se   
      
   Iain Sharp (iains@pciltd.co.uk) writes:   
   > However, when I try to incorporate this script as a trigger on the   
   > table, SQL server returns.   
   >   
   > "Msg 7391, Level 16, State 2, Procedure TRIGGERNAME, Line 51   
   > The operation could not be performed because OLE DB provider "MSDASQL"   
   > for linked server "LINKEDSERVER" was unable to begin a distributed   
   > transaction."   
      
   In addition to Dan's post, getting distributed transactions to work can   
   be a nightmare even when both databases are SQL Server.   
      
   Distributed transactions are handled by MSDTC which is part of the   
   operating system, not SQL Server.   
      
   To start with, there has to be support in the OLE DB provider, or when   
   you use MSDASQL, in the ODBC driver for distributed transactions. How this   
   works with MySQL, I have no idea. But it can be an uphill battle to solve   
   that, particularly if the databases are on different machines. (As this   
   requires two instances of MSDTC to talk with each other.)   
      
   I think you should investigate alternate solution. One approach is to   
   use Service Broker, but this can easily result in the same problem. That   
   is, you don't want to commit getting the message of the queue until you   
   have updated the table. But then you have a distributed transaction   
   again. But you use Service Broker as a triggering mechanism. That   
   is, get the message off the queue into a local table, commit. Then   
   process all unprocess all unprocessed rows in the table, update   
   the MySQL database, if this has not been done. Mark the row as processed.   
   Note that you must be table to handle the case that the MySQL row has   
   been updated, although your worktable says it hasn't. That is, you are   
   rolling your own two-phase commit.   
      
   Not very fun, but I rather do it than fighting MSDTC.   
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
   SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)