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.