Enable Promotion of Distributed Transaction for RPC for Linked Server

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.