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'
BTW, MS describes “remote proc transaction promotion”
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).
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:
- Microsoft – Windows – v2008 – Enable Network Access Securely for MS DTC
- Microsoft – Windows – v2003 – Network DTC Access
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.
- How to create an autonomous transaction in SQL Server v2008