SQL SERVER – FIX: Msg 7395, Level 16, State 2 – Unable to start a nested transaction for OLE DB provider

While playing with linked server from SQL Server to SQL Server, I faced few errors and fixed them as well. It has become a custom to get errors working with Linked Servers almost every time. I personally thought it is worth blogging because I didn’t find any single blog having the cause and solution explained in detail. Let us learn about the OLE DB provider error.

Here is the query which I was running on SMALLSERVER which has linked server to BIGSERVER. It is a four part naming having a format for ServerName. DatabaseName. SchemaName. ObjectName.  Basically, a query is updating a value in a table called DB. Employee in database called SQLAuthority on server BIGSERVER.

BEGIN DISTRIBUTED TRANSACTION
UPDATE BIGSERVER.SQLAuthority.dbo.Employee
SET SALARY = 'Y'
WHERE ID = 5
COMMIT TRANSACTION

It was failing with below error message.

OLE DB provider “SQLNCLI11” for linked server “BIGSERVER” 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 “BIGSERVER” was unable to begin a distributed transaction.

Here is the important information in error message. “The transaction manager has disabled its support for remote/network transactions” which basically indicates issue with setting of DTC. To fix that, we need to go to Start > Run > dcomcnfg and open below interface (this is Windows 2012 R2 Sevrer)

You can also launch the same interface from “Control Panel” > “Administrative Tools > Component Services”. Follow the screen shot and open “Properties” and go to security tab and change setting as below.

Once changed, restart the DTC Service. Now, I got below error.

OLE DB provider “SQLNCLI11” for linked server “BIGSERVER” returned message “Cannot start more transactions on this session.”.
Msg 7395, Level 16, State 2, Line 3
Unable to start a nested transaction for OLE DB provider “SQLNCLI11” for linked server “BIGSERVER”. A nested transaction was required because the XACT_ABORT option was set to OFF.

Above error also tells the action needed – we need to enable the XACT_ABORT in the transaction. Here is the modified version of the query

SET XACT_ABORT ON
GO
BEGIN DISTRIBUTED TRANSACTION
UPDATE BIGSERVER.SQLAUTHORITY.DBO.EMPLOYEE
SET SALARY = 'Y'
WHERE ID = 5
COMMIT TRANSACTION

And that worked for me and it updated the value on remote server.

You must have also encountered some errors while using linked server? Share the problem and solution via comments section.

Reference: Pinal Dave (https://blog.sqlauthority.com)