SQL Server Explicitly Initiated Transactions

A Transact-SQL application can explicitly initiate an MS DTC transaction by calling the Transact-SQL BEGIN DISTRIBUTED TRANSACTION statement. An explicitly initiated transaction works as follows:

When the application calls the Transact-SQL BEGIN DISTRIBUTED TRANSACTION statement, SQL Server calls the MS DTC ITransactionDispenser::BeginTransaction method and obtains a transaction object that represents the transaction. The call to BEGIN DISTRIBUTED TRANSACTION can include a transaction description. The transaction description is displayed by the MS DTC management interface in the MS DTC Transactions window.
After obtaining the transaction object, SQL Server enlists in the transaction with its local MS DTC transaction manager. This permits SQL Server to participate in the two-phase commit protocol and to receive transaction commit or abort notifications from MS DTC.
All database updates, inserts, and deletes that are performed by the application are performed within the MS DTC transaction.
If the application calls a remote stored procedure in another SQL Server, the originating SQL Server propagates the MS DTC transaction with the call to the remote stored procedure, and the remote SQL Server also enlists in the transaction. All changes to both SQL Servers are protected by the MS DTC transaction.
When the work of the transaction is complete, the application calls the Transact-SQL COMMIT TRANSACTION statement. In response to the COMMIT TRANSACTION call, SQL Server calls the MS DTC ITransaction::Commit method. MS DTC uses the two-phase commit protocol to coordinate commitment of the transaction. Alternatively, the application could call the Transact-SQL ROLLBACK TRANSACTION statement. In this case, SQL Server would call the MS DTC ITransaction::Abort method to undo the effects of the transaction from both SQL Servers.
The application can then go on to perform more transactions.

A call to BEGIN DISTRIBUTED TRANSACTION affects subsequent calls to other transaction-related Transact-SQL statements. For more information, see the BEGIN DISTRIBUTED TRANSACTION statement in What's New in SQL Server 6.5.

This example shows how an MS DTC transaction that is explicitly initiated can be used within a stored procedure to ensure that two SQL Server databases are updated consistently. The change_addr stored procedure initiates an MS DTC transaction, updates its local pubs database, calls the update_addr remote stored procedure to update the remote pubs database, and then commits the MS DTC transaction.

/*******************************************************/
/* Using BEGIN DISTRIBUTED TRANSACTION to explicitly   */
/* initiate MS DTC transactions.                       */
/*******************************************************/
CREATE PROCEDURE change_addr(@au_id varchar(11),
                             @addr varchar(40), 
                             @toserver varchar(12) ) AS
declare @execstr varchar(200)
  
/* Start a Transaction */
BEGIN DISTRIBUTED TRANSACTION change_author_address
  
/* Change local author information */
update authors set address = @addr where au_id = @au_id 
  
/* Make a string with the remote server name and       */
/* stored procedure to execute                         */
select @execstr = @toserver + '.pubs..update_addr ' 
  
/* Update remote server. (Note that these servers must */
/* be added to each other by using sp_addserver and    */
/* sp_addremotelogin.)                                 */
exec @execstr @au_id, @addr
  
/* Commit the MS DTC transaction */
COMMIT TRANSACTION

The following remote stored procedure is used to update the remote pubs database.

/********************************************************/
/* Stored procedure to update an author's address on    */
/* the remote server.                                   */
/********************************************************/
CREATE PROCEDURE update_addr(@au_id varchar(11), 
                             @addr varchar(40)) AS
update authors set address = @addr where au_id = @au_id