Specifies that when a local transaction is active, executing a remote stored procedure starts a Transact-SQL distributed transaction managed by the Microsoft Distributed Transaction Manager (MS DTC).
SET REMOTE_PROC_TRANSACTIONS {ON | OFF}
When REMOTE_PROC_TRANSACTIONS is ON, calling a remote stored procedure starts a distributed transaction and enlists the transaction with MS DTC. The server making the remote stored procedure call 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.
After a Transact-SQL distributed transaction has been started, remote stored procedure calls can be made to other remote servers. The remote servers are all enlisted in the Transact-SQL distributed transaction and MS DTC ensures that the transaction is completed against each server.
REMOTE_PROC_TRANSACTIONS is a connection-level setting that can be used to override the server-level sp_configure remote proc trans option.
When REMOTE_PROC_TRANSACTIONS is set OFF, remote stored procedure calls are not made part of a local transaction. The modifications made by the remote stored procedure are committed or rolled back at the time the stored procedure completes. Subsequent COMMIT TRANSACTION or ROLLBACK TRANSACTION statements issued by the connection that called the remote stored procedure have no effect on the processing done by the procedure.
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 on remote store 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 on distributed queries, see Distributed Query Architecture.
The setting of SET REMOTE_PROC_TRANSACTIONS is set at execute or run time and not at parse time.
SET REMOTE_PROC_TRANSACTIONS permissions default to all users.
| BEGIN DISTRIBUTED TRANSACTION | Transactions |
| Distributed Transactions | SET |