When the script or application issues either a COMMIT or ROLLBACK statement, the controlling instance of SQL Server calls MS DTC to manage the two-phase commit process or to notify the linked and remote servers to roll back their transactions.

The Transact-SQL statements controlling the distributed transactions are few because most of the work is done internally by the SQL Server Database Engine and MS DTC. The only Transact-SQL statements required in the Transact-SQL script or application are those required to:

For any Transact-SQL distributed transaction, the instance of the Database Engine processing the Transact-SQL script or connection automatically calls MS DTC to coordinate the commitment or rollback of the transaction.

You can also execute a distributed query against a linked server. The instance of the Database Engine you have connected to calls MS DTC to manage the distributed transaction with the linked server. You can also call remote stored procedures on a remote instance of the Database Engine as part of the distributed transaction.

While in a local transaction, execute a distributed query.

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 instance of the Database Engine, the local transaction is promoted to a distributed transaction.

The Database Engine uses MS DTC to coordinate the transaction with the remote server. Calls to remote stored procedures execute outside the scope of a local transaction if REMOTE_PROC_TRANSACTIONS is set to OFF. The work done by the remote procedure is not rolled back if the local transaction is rolled back. The work done by the remote stored procedure is committed at the time the procedure completes, not when the local transaction is committed.

The REMOTE_PROC_TRANSACTIONS option is a compatibility option that affects only remote stored procedure calls made to remote servers defined using sp_addserver. The option does not apply to distributed queries that execute a stored procedure on a linked server defined using sp_addlinkedserver. For more information about distributed queries, see Distributed Queries.