SQL Server Implicitly Initiated Transactions

The Transact-SQL BEGIN DISTRIBUTED TRANSACTION statement provides an explicit way for a Transact-SQL application to begin a distributed transaction. However, you must change existing Transact-SQL applications to initiate MS DTC transactions in this way. SQL Server provides a way for you to use MS DTC transactions without any change to your existing Transact-SQL application. SQL Server implicitly initiated transactions share all of the benefits of explicitly initiated transactions but have these additional advantages:

• Existing Transact-SQL applications can use distributed transactions with no change to the application.
• Application programming is easy because SQL Server automatically initiates distributed transactions only when they are needed. SQL Server only initiates an MS DTC transaction when no distributed transaction is in force and a Transact-SQL application in one SQL Server calls a remote stored procedure in another SQL Server.

SQL clients and the SQL system administrator control whether SQL Server implicitly initiates MS DTC transactions. There are two SQL Server options for controlling implicit transactions:

• The REMOTE_PROC_TRANSACTIONS option is a session-level option that the Transact-SQL application controls by using the Transact-SQL SET statement. When this session-level option is set and a Transact-SQL application calls a remote stored procedure, SQL Server automatically initiates an MS DTC transaction.
• The REMOTE PROC TRANS option is a server configuration parameter that the system administrator controls by using the sp_configure statement. The REMOTE PROC TRANS server configuration parameter determines the default setting of the REMOTE_PROC_TRANSACTIONS session-level option. It permits the system administrator to enable implicit MS DTC transactions for an entire SQL Server system.

The REMOTE_PROC_TRANSACTIONS session-level option and the REMOTE PROC TRANS server configuration parameter have no affect on programs that initiate MS DTC transactions explicitly by using the BEGIN DISTRIBUTED TRANSACTION statement.

The first example illustrates the REMOTE_PROC_TRANSACTIONS session-level option and the second illustrates the REMOTE PROC TRANS server configuration parameter.