March 6, 2018 at 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.
March 6, 2018 at 4:24 pm
Your temp table will not be accessible as sp_executesql runs your SQL in a different session then the one you create the temp table in. You would need to make it global ##Myatran. Also enable distributed transaction, RPC and RPC out on the linked server. when you insert from a local table into a table on a linked server, that is a distributed transaction.
March 6, 2018 at 4:38 pm
ayemya - Tuesday, March 6, 2018 2:22 PMI 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 OFFThank you.
As Joe said, it is a distributed transaction. You don't need to use begin distributed transaction for it to be promoted to a distributed transaction. You do need to configure the Distributed Transaction Coordinator. There are some steps in this related article - refer to the Distributed Transaction Coordinator section for the steps:
Setting up linked servers with an out-of-process OLEDB provider
Sue
March 7, 2018 at 10:05 am
I appreciate your answers. It explains why my transaction throws errors. we have SQL 2014 AG and it doesnt support DTC. I am trying to think a workaround. Thank you.
March 7, 2018 at 12:05 pm
ayemya - Wednesday, March 7, 2018 10:05 AMI appreciate your answers. It explains why my transaction throws errors. we have SQL 2014 AG and it doesnt support DTC. I am trying to think a workaround. Thank you.
You should be able to do it in Powershell - you can use write-datatable if you are using the SQLServer module. Otherwise the same cmdlet has been written as a function you can download from the TechNet script repository:
Write-DataTable
Sue
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply