Use this option to protect the actions of a server-to-server procedure through a Microsoft Distributed Transaction Coordinator (MS DTC) transaction. When this option is TRUE (or ON) calling a remote stored procedure starts a distributed transaction and enlists the transaction with MS DTC. The instance of SQL 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 instance requests that MS DTC manage the completion of the distributed transaction across the computers involved.
After a Transact-SQL distributed transaction has been started, remote stored procedure calls can be made to other instances of SQL Server that have been defined as linked servers. The linked servers are all enlisted in the Transact-SQL distributed transaction, and MS DTC ensures that the transaction is completed against each linked server.
If this option is set to FALSE (or OFF), a local transaction will not be promoted to a distributed transaction while calling a remote procedure call on a linked server.
If before making a server-to-server procedure call, the transaction is already a distributed transaction, then this option does not have effect. The procedure call against linked server will run under the same distributed transaction.
If before making a server-to-server procedure call, there is no transaction active in the connection, then this option does not have effect. The procedure then runs against linked server without active transactions.
The default value for this option is TRUE (or ON).