INF: Distributed Queries Executed Within a Trigger Enlist MSDTC (274348)

MORE INFORMATION

SQL Server Books Online mentions that distributed transactions in Transact-SQL are invoked in three ways:

• When you start an explicit distributed transaction using the BEGIN DISTRIBUTED TRANSACTION statement.
• When you execute a distributed query while in a local transaction. If the OLE DB data source supports the ITransactionJoin interface, the transaction is promoted to a distributed transaction, even if the query is a read-only query. If the data source does not support ITransactionJoin, only read-only statements are allowed.
• If SET REMOTE_PROC_TRANSACTIONS ON has been executed and a local transaction calls a remote stored procedure on another SQL Server, the local transaction is promoted to a distributed transaction. SQL Server uses Microsoft Distributed Transaction Coordinator (MSDTC) to coordinate the transaction with the remote server.

A distributed query that is wrapped in a trigger, even with no transaction explicitly specified, is also treated as a distributed transaction.

To see an example, follow these steps:

1. Stop the MSDTC service on the local server by typing the following from a command window:

   net stop MSDTC

2. Execute the following code in Query Analyzer:

   USE tempdb
   go
   
   CREATE TABLE t1 (col1 int, col2 varchar(10))
   go
   
   INSERT t1 VALUES (1, 'a')
   go
   
   EXEC SP_ADDLINKEDSERVER 'SQL1', N'SQL Server'
   go
   
   CREATE TRIGGER t1_ins ON t1
   FOR INSERT AS
     PRINT 'This is a test for distributed transaction.  The current trancount is ' + convert(char(1), @@trancount) + '.'
     PRINT ' '
     EXEC SQL1.master.dbo.sp_helpdb
   go
   					

3. After the trigger is created on the table t1, execute the following INSERT query:

   INSERT t1 VALUES (2, 'b')
   					

   An MSDTC error is generated, and the query output is as follows:

   (1 row(s) affected)
   
   (1 row(s) affected)
   
   (1 row(s) affected)
   
   Server added.
   This is a test for distributed transaction.  The current trancount is 1.
    
   Server: Msg 8501, Level 16, State 3, Procedure t1_ins, Line 6
   MSDTC on server 'SQL1' is unavailable.<BR/>
   					

If the distributed query does not need to be committed/rolled back with a local transaction as a single unit of work, you can add a COMMIT TRAN T-SQL command in front of the distributed query to force the local transaction to commit. The distributed query will be automatically removed from the transaction.