3

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.

5

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

SET XACT_ABORT ON;

See Distributed Queries and Distributed Transactions

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.