Transact-SQL Distributed Transactions

The distributed transactions started in Transact-SQL have a relatively simple structure:

1. A Transact-SQL script or application connection executes a Transact-SQL statement that starts a distributed transaction.
2. The Microsoft® SQL Server™ executing the statement becomes the controlling server in the transaction.
3. The script or application then executes either distributed queries against linked servers or remote stored procedures against remote servers.
4. As distributed queries and remote procedure calls are made, the controlling server automatically calls MS DTC to enlist the linked and remote servers in the distributed transaction.
5. When the script or application issues either a COMMIT or ROLLBACK statement, the controlling 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.

Required Transact-SQL Statements

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

• Start a distributed transaction.
• Perform distributed queries against linked servers or execute remote procedure calls against remote servers.
• Call the standard Transact-SQL COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, or ROLLBACK WORK statements to complete the transaction.

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

Starting Distributed Transactions

You can start distributed transactions in Transact-SQL in these ways:

• Start an explicit distributed transaction using the BEGIN DISTRIBUTED TRANSACTION statement.

  You can also execute a distributed query against a linked server. The SQL Server 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 SQL Server 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 SQL Server, the local transaction is promoted to a distributed transaction.

  SQL Server 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. For more information about remote stored procedures, see Remote Stored Procedure Architecture. 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.

See Also