OLE DB provider "SQLNCLI10" for linked server "XXX" returned message "The partner transaction manager has disabled its support for remote/network transactions.".

• garima.arya

  Say Hey Kid

  Points: 681

  More actions

  May 3, 2011 at 12:15 am

  #250145

  Hi,

  I have two SQL Servers Server A and Server B. ServerA has SQL Server 2008 and ServerB has SQL Server 2005.

  I need to fetch data from server B to server A and insert it into a table on server A.

  For this purpose, I have created a stored proc on server B and for calling that SP I have added server B as linked server on server A.

  When I execute this SP from server A using server B, it returns data without any issue.

  However when I try to insert this data to a local table on server A, I am getting following error:

  OLE DB provider "SQLNCLI10" for linked server "ServerB" returned message "The partner transaction manager has disabled its support for remote/network transactions.".

  Msg 7391, Level 16, State 2, Line 12

  The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "ServerB" was unable to begin a distributed transaction.

  Note that I have not started any transaction in both the calling and called procs. I am not getting why it is trying to begin a distributed transaction. And how to resolve this issue.

  I am using linked server for the first time. Please help.

  Thanks,

  Garima

• garima.arya

  Say Hey Kid

  Points: 681

  More actions

  May 3, 2011 at 5:22 am

  #1318973

  Hi All,

  I have been able to resolve the issue after executing the following command on server A:

  EXEC sp_serveroption @server = 'ServerB',@optname = 'remote proc transaction promotion', @optvalue = 'false' ;

• Michael-401546

  Mr or Mrs. 500

  Points: 557

  More actions

  February 17, 2012 at 1:44 pm

  #1448772

  WORKS GREAT!!!!!!

  Here is what I did to fix ALL my linked servers

  DECLARE @ServerName SYSNAME

  , @Message nvarchar(1000)

  , @CMD1 nvarchar(max)

  --

  DECLARE @Server_List Table

  ( SrvID SMALLINT

  , SrvName SYSNAME )

  --

  Set NoCount ON

  --

  -- Load up linked server list

  --

  BEGIN

  INSERT INTO @Server_List (SrvID, SrvName)

  SELECT SrvID

  , SrvName

  FROM [master].[SYS].sysservers

  ORDER BY SrvID ASC

  END

  --

  SELECT TOP 1 @ServerName = SrvName

  FROM @Server_List

  ORDER BY SrvID ASC

  --

  -- Loop through the Linked Server List

  --

  WHILE EXISTS ( SELECT * FROM @Server_List )

  BEGIN

  SELECT @Message = 'Server Name is '+ @ServerName

  --

  RAISERROR (@Message, 10,1) WITH NOWAIT

  --

  SET @CMD1 = 'EXEC master.dbo.sp_serveroption @server=N'''

  + @ServerName

  + ''', @optname=N''rpc'', @optvalue=N''true'''

  Exec sp_executesql @cmd1

  --

  SET @CMD1 = 'EXEC master.dbo.sp_serveroption @server=N'''

  + @ServerName

  + ''', @optname=N''rpc out'', @optvalue=N''true'''

  Exec sp_executesql @cmd1

  --

  set @cmd1 = 'EXEC master.dbo.sp_serveroption @server = '''

  + @ServerName

  + ''', @optname=N''remote proc transaction promotion'', @optvalue=N''false'''

  Exec sp_executesql @stmt=@cmd1,@params=N''

  --

  DELETE FROM @Server_List WHERE SrvName = @ServerName

  --

  SELECT TOP 1 @ServerName = SrvName

  FROM @Server_List

  ORDER BY SrvID ASC

  --

  END

• castellillc

  SSC Journeyman

  Points: 83

  More actions

  June 27, 2012 at 9:47 am

  #1506232

  Worked perfectly! Thanks so much!

  Eric

• Martin-226012

  SSC Enthusiast

  Points: 152

  More actions

  July 16, 2012 at 7:45 am

  #1513069

  I'm going to cry. I had the same problem.

  It works perfectly !!!

  Thanks so much

• Mr. Belvedere

  SSC Enthusiast

  Points: 148

  More actions

  July 19, 2012 at 10:49 am

  #1515003

  I had this problem between two SQL2005 servers, but your solution does not work for 2005 since

  sp_serveroption optname "remote proc transaction promotion" is SQL2008 and up.

  Changing the Windows component install does, that is "Enabling network DTC access" does. See the following:

  http://connect.microsoft.com/sqlserver/feedback/details/243725/linked-server-sourceserver-was-unable-to-begin-a-distributed-transaction

• giacomo_mauro_perticone

  SSC Enthusiast

  Points: 100

  More actions

  June 27, 2013 at 5:14 am

  #1627914

  THANK YOU SOO MUCH ...

  I was really struggling with this for 2 days !!!

• alejandrogutierrezdiaz

  Valued Member

  Points: 53

  More actions

  August 20, 2013 at 8:11 am

  #1643088

  Señores, gracias por este gran aporte

• trilist

  SSC Eights!

  Points: 902

  More actions

  April 2, 2015 at 9:07 am

  #1788346

  Works Great! Thanks for sharing!

• Mohamed Rafeek-324313

  SSC Journeyman

  Points: 81

  More actions

  December 3, 2015 at 9:33 pm

  #1843969

  Works great. Thanks much

• November 29, 2016 at 4:23 am

  This was removed by the editor as SPAM

• John D

  Right there with Babe

  Points: 787

  More actions

  March 5, 2020 at 12:56 am

  #3731226

  This solved my problem.

  Thank you so much!