devxlogo

Instructing SQL Server to Participate in a Distributed Transaction

Instructing SQL Server to Participate in a Distributed Transaction

Besides using a BEGIN DISTRIBUTED TRANSACTION statement, another way to instruct SQL Server to participate in a distributed transaction and inform the Microsoft Distributed Transaction Coordinator (MS DTC) of that participation is by enabling the remote REMOTE_PROC_TRANSACTIONS option in the SET statement. The syntax is as follows:

 SET REMOTE_PROC_TRANSACTIONS {ON | OFF}

This session-level SET option overrides the server default established by sp_configure. If this option is ON, it creates an MS DTC transaction to cover an RPC (Remote Procedure Call) if an MS DTC transaction does not exist. The server that creates the MS DTC transaction becomes the originating site for the transaction.

devx-admin

Share the Post: