SQL Server – Linked Server – Error – “The partner transaction manager has disabled its support for remote/network transactions”.

Background

Have a little sql script that aggregates data from a couple of DB Servers.


    set nocount on;

    declare @tblSales TABLE
    (

          [id] int not null identity(1,1)
        , [serverName] sysname not null
        , [sales] bigint null

    )

    insert into @tblSales
    (
          [serverName]
        , [sales] 

    )
    exec [HRDB001].[dbHR].dbo.usp_getSalesData

    insert into @tblSales
    (
          [serverName]
        , [sales]
    )
    exec [HRDB002].[hrdb].dbo.usp_getSalesData

But, we were getting the error message:


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

Msg 7391, Level 16, State 2, Line 20

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

The problem was traced back to how we configured the Linked Server:-

We had accepted the default option of setting “remote proc transaction promotion” as true.

Here is the auto-generated script.


      EXEC master.dbo.sp_serveroption
	       @server=HRDB
             , @optname=N'remote proc transaction promotion'
             , @optvalue=N'true'

To determine the current configurations, issue:


SELECT
          srv.name AS [Name]
        , CAST(srv.server_id AS int) AS [ID]
        , product
        , data_source
        , srv.modify_date AS [DateLastModified]
        , srv.is_remote_proc_transaction_promotion_enabled
        , CAST(srv.is_remote_proc_transaction_promotion_enabled AS bit)
            AS [IsPromotionofDistributedTransactionsForRPCEnabled]
        , srv.provider_string AS [ProviderStringIn]
FROM sys.servers AS srv

To reconfigure, issue:


      EXEC master.dbo.sp_serveroption
	       @server=HRDB
             , @optname=N'remote proc transaction promotion'
             , @optvalue=N'false'

Microsoft Definition

BTW, MS describes “remote proc transaction promotion”

http://msdn.microsoft.com/en-us/library/ms178532.aspx

Use this option to protect the actions of a server-to-server procedure through a Microsoft Distributed Transaction Coordinator (MS DTC) transaction. When this option is TRUE (or ON) calling a remote stored procedure starts a distributed transaction and enlists the transaction with MS DTC. The instance of SQL Server making the remote stored procedure call is the transaction originator and controls the completion of the transaction. When a subsequent COMMIT TRANSACTION or ROLLBACK TRANSACTION statement is issued for the connection, the controlling instance requests that MS DTC manage the completion of the distributed transaction across the computers involved.

After a Transact-SQL distributed transaction has been started, remote stored procedure calls can be made to other instances of SQL Server that have been defined as linked servers. The linked servers are all enlisted in the Transact-SQL distributed transaction, and MS DTC ensures that the transaction is completed against each linked server.

If this option is set to FALSE (or OFF), a local transaction will not be promoted to a distributed transaction while calling a remote procedure call on a linked server.

If before making a server-to-server procedure call, the transaction is already a distributed transaction, then this option does not have effect. The procedure call against linked server will run under the same distributed transaction.

If before making a server-to-server procedure call, there is no transaction active in the connection, then this option does not have effect. The procedure then runs against linked server without active transactions.

The default value for this option is TRUE (or ON).

My Understanding

In a nutshell, MS default’s setting is to treat RPC calls (exec <remoteserver>.<catalog>.<schema>.<storedProcedure>) as needing DTC Processing.

This makes sense since RPC calls can encapsulate multiple SQL statements.  And, these statements can potentially change data – They can include insert, update, and delete.  

So defensively, MS assumes that RPC calls can have side-effects, that is change data, and thus they are automatically placed in a protective garment.

When you know that you do not need the automatic protection, then definitely set “remote proc transaction promotion” to false.

On the other hand, if you do need DTC (Distributed Transaction Co-ordinator), and forgive me but we use to call DTC [ two-phase commit]), then put your gloves on for extensive debugging, which includes:

And, many tickets and diagnostic hours with your Network Firewall Team, as they just love MS-DTC usage of MS-RPC and its foundational reliance on Ephemeral ports.

Additional Reading:

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s