REMOTE_PROC_TRANSACTIONS Session Option

The REMOTE_PROC_TRANSACTIONS option is a session-level option that the Transact-SQL application controls by using the Transact-SQL SET statement. When the session-level option is ON and a Transact-SQL application calls a remote stored procedure, SQL Server implicitly initiates an MS DTC transaction. When this session-level option is OFF, no MS DTC transactions are implicitly initiated on the session. The session-level option is controlled by using the Transact-SQL SET statement as follows:

set remote_proc_transactions {ON|OFF}
  

For details about REMOTE_PROC_TRANSACTIONS, see What's New in SQL Server 6.5.

A SQL Server implicitly initiated transaction works as follows:

1. When a Transact-SQL application calls the BEGIN TRANSACTION statement, SQL Server initiates a local SQL Server transaction. It does not initiate an MS DTC transaction.
2. All changes that the Transact-SQL application makes to the local database are performed within the local transaction.
3. If the Transact-SQL application calls a remote stored procedure in another SQL Server system, SQL Server determines if the REMOTE_PROCEDURE_TRANSACTIONS session-level option has been enabled. If so, SQL Server initiates an MS DTC transaction by calling the ITransactionDispenser::BeginTransaction method and obtaining a transaction dispenser object that represents the MS DTC transaction. 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.
4. SQL Server propagates the MS DTC transaction with the call to the remote stored procedure. All updates, inserts, and deletes (including those that the Transact-SQL application had previously made to the local database within the local transaction) will now be protected within the MS DTC transaction.
5. When the work of the transaction is complete, the Transact-SQL application calls the COMMIT TRANSACTION statement. SQL Server determines whether a local or MS DTC transaction is in force. If an MS DTC transaction is in force, 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 Transact-SQL application could call the ROLLBACK TRANSACTION statement. In this case, SQL Server calls the MS DTC ITransaction::Abort method to undo the effects of the transaction.
6. The Transact-SQL application then can go on to perform more transactions.

   For information about the BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK TRANSACTION statements, see the Microsoft SQL Server Transact-SQL Reference.

This example shows how SQL Server implicitly initiates a distributed transaction when one is needed and the REMOTE_PROC_TRANSACTIONS session-level option is ON. Note that the Transact-SQL application continues to use the BEGIN TRANSACTION statement rather than the new BEGIN DISTRIBUTED TRANSACTION statement.

/*******************************************************/
/* Using the session option for implicit SQL Server    */
/* initiated transactions.                             */
/*******************************************************/
  
CREATE PROCEDURE change_addr(@au_id varchar(11),
                             @addr varchar(40),
                             @toserver varchar(12) ) AS
declare @execstr varchar(200)
  
/* Set the REMOTE_PROC_TRANSACTIONS option to enable   */
/* MS DTC transactions                                 */
set remote_proc_transactions ON
  
/* Begin a local SQL Server Transaction.               */
/* This transaction will become an MS DTC distributed  */ 
/* transaction when the remote stored procedure is     */
/* called because the REMOTE_PROC_TRANSACTIONS option  */ 
/* is ON.                                              */
BEGIN TRANSACTION
  
/* 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. This statement causes SQL     */
/* Server to begin an MS DTC distributed transaction.  */
/* (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