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
Best Answer
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:
Here, I'm creating a local table in tempdb to hold some rows:
a quick check to see if we have rows in the source table:
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.
The output:
However, if you run the insert from the target server, like this:
You see this in the trace:
Clearly, it pays to run the insert at the target server, instead of the source server.