Begins a distributed transaction.
BEGIN DISTRIBUTED TRANsaction [transaction_name]
where
The BEGIN DISTRIBUTED TRANsaction statement is used instead of the BEGIN TRANsaction statement when a transaction is to be processed on one or more remote servers. The command alerts MS DTC to manage the transaction processing on the local and remote servers.
For example, suppose a company has several marketing departments in different physical locations. At each location a server is running a database. The company headquarter database records the company marketing budget and the amounts of money that subsidiary offices have to produce events and run their various departments. Each subsidiary's database allocates budget and costs. When the company's headquarters issues a check to the subsidiary, headquarters records the debit to its local database and also credits the database on the remote subsidiary's server. If the transaction fails, then the transaction must not be recorded on one server and not the other. The BEGIN DISTRIBUTED TRANsaction statement protects the transaction so that records of the budget cannot be lost between servers.
The BEGIN DISTRIBUTED TRANsaction statement requests that the MS DTC begin a new transaction and enlist the requester in that transaction.
The requester who issued the BEGIN DISTRIBUTED TRANsaction statement is the transaction originator, and a subsequent COMMIT TRANsaction statement causes the server to ask the MS DTC to start the commit sequence. The MS DTC transactions started by SQL Server cannot be exported to other servers because the client is not able to pass the transaction object from one server to another server.
To protect server-to-server remote procedure calls (RPCs) under MS DTC control without changing existing applications, a server configuration option is provided, remote proctrans in sp_configure, to implicitly create MS DTC transactions that cover server-to-server RPCs.
The session-level SET option, REMOTE_PROC_TRANSACTIONS, overrides the server default established by sp_configure. With this option in effect, an MS DTC transaction is created to cover an RPC if an MS DTC transaction does not exist. The server that creates the MS DTC transaction becomes the originating site for the transaction. Provided any nested transactions have been committed, the COMMIT TRANsaction initiates an MS DTC coordinated commit.
For more information about the distributed transaction environment and process, see the Guide to Microsoft Distributed Transaction Coordinator.
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.
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