Microsoft KB Archive/274348

= INF: Distributed Queries Executed Within a Trigger Enlist MSDTC =

Article ID: 274348

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q274348



SUMMARY
A query that is executed within the context of a trigger is automatically wrapped in a transaction. If there are any distributed queries in the trigger code, the transaction is promoted to a distributed transaction automatically.



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:  Stop the MSDTC service on the local server by typing the following from a command window:

net stop MSDTC

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

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.

Additional query words: Distributed Transaction, Stored Procedure and Trigger

Keywords: kbinfo KB274348

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.