Applications that are written in Transact-SQL can use MS DTC transactions. Using MS DTC through Transact-SQL stored procedures is attractive for these reasons:
Transact-SQL applications can initiate MS DTC transactions in two ways: They can explicitly initiate transactions by calling the Transact-SQL BEGIN DISTRIBUTED TRANSACTION statement, or they can rely on SQL Server to implicitly initiate MS DTC transactions when they are needed. Both of these techniques are illustrated in the examples that follow.
When a Transact-SQL application uses MS DTC transactions, the application can call a remote stored procedure in another SQL Server under the protection of a single distributed transaction. SQL Server and MS DTC automatically propagate the MS DTC transaction from one SQL Server to the other. If the remote stored procedure calls other remote stored procedures in other SQL Servers, these SQL Servers will also participate in the transaction. Transaction propagation occurs regardless of whether the transaction was initiated explicitly or implicitly.
If SQL Server and MS DTC encounter an error while propagating an MS DTC transaction from one system to another, the entire transaction is aborted. This might occur, for example, if the network connection between the two systems fails.
SQL Server initiates and propagates MS DTC transactions for applications written in Transact-SQL. However, Transact-SQL applications cannot directly access MS DTC transaction objects. They cannot return MS DTC transaction objects to the client application that called them. They cannot propagate MS DTC transactions objects to extended stored procedures that they call. In short, MS DTC transactions initiated by SQL Server cannot be directly manipulated by the Transact-SQL application or exported outside of SQL Server by the Transact-SQL application.