EXEC master.dbo.sp_serveroption
     @server = N'[mylinkedserver]',
     @optname = N'remote proc transaction promotion',
     @optvalue = N'false'

Also configured in the Linked Server Properties “Server Options” section called “Enable Promotion of Distributed Transactions for RPC”.

remote proc transaction promotion‘ is a new option on SQL Server 2008, which allows you to control whether or not you want to enlist remote stored procedure call in a distributed transaction.¬†When this option is off (FALSE), the local transaction will not be promoted to distributed transaction.¬†This is how we are able to separate outer and inner transactions in a “autonomous transaction” fashion.

Last modified: September 18, 2020



Write a Reply or Comment