ayemya - Tuesday, March 6, 2018 2:22 PM

I have a database in SQL 2014 AG group.- two nodes. On both nodes, a linked server is set up. I am selecting data from a table from a database from AG group and insert into a linked server (a database from another AG group) . only insert statement is in Transaction block.  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

Thank you.