Unable to start a nested transaction for OLE DB provider "SQLNCLI11" for linked server

I have two linked servers and want to create a stored procedure that simply SELECTs data from one table from the first server and INSERT the data in other table from the second server.

The procedure code looks like this:

BEGIN TRAN

INSERT INTO [RI].[TEST_DB].[TEST_TABLE] (...)
SELECT ...
FROM [TABLE]

TRUNCATE [TABLE]

COMMIT TRAN

But I get the following error:


OLE DB provider "SQLNCLI11" for linked server "RI" returned message "Cannot start more transactions on this session.".

Msg 7395, Level 16, State 2, Procedure usp_test, Line 46

Unable to start a nested transaction for OLE DB provider "SQLNCLI11" for linked server "RI".

A nested transaction was required because the XACT_ABORT option was set to OFF.

As the error message suggests, add the following to your stored procedure:

SET XACT_ABORT ON;

See Distributed Queries and Distributed Transactions

StackExchange Database Administrators Q#90774, answer score: 6