Specifies the start of a Transact-SQL distributed transaction managed by Microsoft Distributed Transaction Coordinator (MS DTC).
BEGIN DISTRIBUTED TRAN[SACTION]
[transaction_name | @tran_name_variable]
The server executing the BEGIN DISTRIBUTED TRANSACTION statement is the transaction originator and controls the completion of the transaction. When a subsequent COMMIT TRANSACTION or ROLLBACK TRANSACTION statement is issued for the connection, the controlling server requests that MS DTC manage the completion of the distributed transaction across the servers involved.
There are two ways remote SQL servers are enlisted in a distributed transaction:
For example, if BEGIN DISTRIBUTED TRANSACTION is issued on ServerA, the connection calls a stored procedure on ServerB and another stored procedure on ServerC, and the stored procedure on ServerC executes a distributed query against ServerD, then all four SQL servers are involved in the distributed transaction. ServerA is the originating, controlling server for the transaction.
The connections involved in Transact-SQL distributed transactions do not get a transaction object they can pass to another connection for it to explicitly enlist in the distributed transaction. The only way for a remote server to enlist in the transaction is to be the target of a remote stored procedure call or a distributed query.
The sp_configure remote proc trans option controls whether calls to remote stored procedures in a local transaction automatically cause the local transaction to be promoted to a distributed transaction managed by MS DTC. The connection-level SET option REMOTE_PROC_TRANSACTIONS can be used to override the server default established by sp_configure remote proc trans. With this option set on, a remote stored procedure call causes a local transaction to be promoted to a distributed transaction. The connection that creates the MS DTC transaction becomes the originator for the transaction. COMMIT TRANSACTION initiates an MS DTC coordinated commit. If the sp_configure remote proc trans option is set on, remote stored procedure calls in local transactions are automatically protected as part of distributed transactions without having to rewrite applications to specifically issue BEGIN DISTRIBUTED TRANSACTION instead of BEGIN TRANSACTION.
When a distributed query is executed in a local transaction, the transaction is automatically promoted to a distributed transaction if the target OLE DB data source supports ITransactionLocal. If the target OLE DB data source does not support ITransactionLocal, only read-only operations are allowed in the distributed query.
For more information about the distributed transaction environment and process, see your Microsoft Distributed Transaction Coordinator documentation.
BEGIN DISTRIBUTED TRANSACTION permissions default to any valid user.
This example updates the author’s last name on the local and remote databases. The local and remote databases will both either commit or roll back the transaction.
Note Unless MS DTC is currently installed on the computer running Microsoft® SQL Server™, this example produces an error message. For more information about installing MS DTC, see your Microsoft Distributed Transaction Coordinator documentation.
USE pubs
GO
BEGIN DISTRIBUTED TRANSACTION
UPDATE authors
SET au_lname = 'McDonald' WHERE au_id = '409-56-7008'
EXECUTE remote.pubs.dbo.changeauth_lname '409-56-7008','McDonald'
COMMIT TRAN
GO
| BEGIN TRANSACTION | COMMIT WORK |
| COMMIT TRANSACTION | ROLLBACK WORK |
| ROLLBACK TRANSACTION | Distributed Transactions |
| SAVE TRANSACTION |