Post reply

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

  • 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

  • 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' ;

  • 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

  • June 27, 2012 at 9:47 am

    #1506232

    Worked perfectly! Thanks so much!

    Eric

  • July 16, 2012 at 7:45 am

    #1513069

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

    It works perfectly !!!

    Thanks so much

  • 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

  • June 27, 2013 at 5:14 am

    #1627914

    THANK YOU SOO MUCH ...

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

  • August 20, 2013 at 8:11 am

    #1643088

    Señores, gracias por este gran aporte

  • April 2, 2015 at 9:07 am

    #1788346

    Works Great! Thanks for sharing!

  • 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

  • March 5, 2020 at 12:56 am

    #3731226

    This solved my problem.

    Thank you so much!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply