"SQLNCLI11" for linked server "linkedserver" returned message "The transaction manager has disabled its support for remote/network transactions."

Question

I have a database in a SQL Server 2014 AG group (AG1).- two nodes.

On both nodes, a linked server is set up. I am selecting data from a table from AG group (AG1) database and inserting into a linked server (a database from another AG group(AG2)). The only statement in the transaction is an insert statement. It is not a distributed transaction.

Why do i get the following error?

OLE DB provider "SQLNCLI11" for linked server "LinkedserverListener" returned message "The transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "LinkedServerListener" was unable to begin a distributed transaction.

Sample code is a below.

Drop table #Myatran
Select 'ABC' name into #Myatran

DECLARE @pndstop_ps NVARCHAR(4000)
SET @pndstop_ps = 
N'
INSERT INTO '+ 'linkedserver'+'.DB.dbo.MyaTEST(Name) SELECT name FROM #Myatran
'
SET XACT_ABORT ON
BEGIN TRANSACTION

EXEC sp_executeSQl @pndstop_ps

SET XACT_ABORT OFF

Answer 1

SQL Server 2014 Availability Groups does not support distributed transactions.

SQL Server 2016 has limited support for distributed transactions, however, SQL Server 2017 has the best support.

Consider using SQL Server Integration Services or bcp in/out, or some other tool for Extract-Transform-Load, or ETL, operations.

FYI, you may not be aware of this, but doing an INSERT INTO ... into a table on a linked server results in the local SQL Server performing a series of cursor calls for each row inserted into the target table.

To prove this, I've created a linked server and ran the following code to setup a simple test-bed.

First, we'll create a table in tempdb at the linked server:

DECLARE @cmd nvarchar(max);
SET @cmd = 'IF OBJECT_ID(''dbo.InsertTest'', N''U'') IS NOT NULL
DROP TABLE dbo.InsertTest;
CREATE TABLE dbo.InsertTest
(
    id int NOT NULL PRIMARY KEY CLUSTERED
);';

EXEC [LinkedServer].tempdb.sys.sp_executesql @cmd;

Here, I'm creating a local table in tempdb to hold some rows:

IF OBJECT_ID('dbo.InsertTest', N'U') IS NOT NULL
DROP TABLE dbo.InsertTest;CREATE TABLE dbo.InsertTest
(
    id int NOT NULL PRIMARY KEY CLUSTERED
);

INSERT INTO dbo.InsertTest (id)
SELECT ROW_NUMBER() OVER (ORDER BY c.object_id, c.column_id)
FROM sys.columns c;

a quick check to see if we have rows in the source table:

SELECT CountOfRows = COUNT(1)
FROM dbo.InsertTest;

╔═════════════╗
║ CountOfRows ║
╠═════════════╣
║         990 ║
╚═════════════╝

Here, we'll insert rows from the local source table into the remote linked-server table. I've setup a trace running on the target server so we can see the T-SQL statements issued against it.

INSERT INTO [LinkedServer].tempdb.dbo.InsertTest (ID)
SELECT it.ID
FROM dbo.InsertTest it;

The output:

╔═════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║                                             SET XACT_ABORT OFF                                              ║
╠═════════════════════════════════════════════════════════════════════════════════════════════════════════════╣
║ declare @p1 int                                                                                             ║
║ set @p1=NULL                                                                                                ║
║ declare @p2 bigint                                                                                          ║
║ set @p2=NULL                                                                                                ║
║ exec [sys].sp_getschemalock @p1 output,@p2 output,N'"tempdb"."dbo"."InsertTest"'                            ║
║ select @p1, @p2                                                                                             ║
║ declare @p1 int                                                                                             ║
║ set @p1=NULL                                                                                                ║
║ declare @p3 int                                                                                             ║
║ set @p3=229378                                                                                              ║
║ declare @p4 int                                                                                             ║
║ set @p4=294916                                                                                              ║
║ declare @p5 int                                                                                             ║
║ set @p5=NULL                                                                                                ║
║ exec sp_cursoropen @p1 output,N'select * from "tempdb"."dbo"."InsertTest"',@p3 output,@p4 output,@p5 output ║
║ select @p1, @p3, @p4, @p5                                                                                   ║
║ exec sp_cursor 180150027,4,0,N'[tempdb].[dbo].[InsertTest]',@id=1                                           ║
║ exec sp_cursor 180150027,4,0,N'[tempdb].[dbo].[InsertTest]',@id=2                                           ║
║ exec sp_cursor 180150027,4,0,N'[tempdb].[dbo].[InsertTest]',@id=3                                           ║
║ exec sp_cursor 180150027,4,0,N'[tempdb].[dbo].[InsertTest]',@id=4                                           ║
║ exec sp_cursor 180150027,4,0,N'[tempdb].[dbo].[InsertTest]',@id=5                                           
.
.
║ exec sp_cursor 180150027,4,0,N'[tempdb].[dbo].[InsertTest]',@id=987                                         ║
║ exec sp_cursor 180150027,4,0,N'[tempdb].[dbo].[InsertTest]',@id=988                                         ║
║ exec sp_cursor 180150027,4,0,N'[tempdb].[dbo].[InsertTest]',@id=989                                         ║
║ exec sp_cursor 180150027,4,0,N'[tempdb].[dbo].[InsertTest]',@id=990                                         ║
║ exec [sys].sp_releaseschemalock 1                                                                           ║
║ exec sp_cursorclose 180150027                                                                               ║
║ exec sp_reset_connection                                                                                    ║
╚═════════════════════════════════════════════════════════════════════════════════════════════════════════════╝

However, if you run the insert from the target server, like this:

INSERT INTO dbo.InsertTest (ID)
SELECT it.ID
FROM [LinkedServer].tempdb.dbo.InsertTest it;

You see this in the trace:

╔════════════════════════╦════════════════════════════════════════════════╗
║        TextData        ║                                                ║
╠════════════════════════╬════════════════════════════════════════════════╣
║ SET STATISTICS XML ON  ║                                                ║
║                        ║ INSERT INTO dbo.InsertTest (ID)                ║
║                        ║ SELECT it.ID                                   ║
║                        ║ FROM [CP708-D377\MV].tempdb.dbo.InsertTest it; ║
║ SET STATISTICS XML OFF ║                                                ║
╚════════════════════════╩════════════════════════════════════════════════╝

Clearly, it pays to run the insert at the target server, instead of the source server.