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

SQL
15 Comments

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)

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

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.

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

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)

SQL Error Messages, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Working with Event Viewer and PowerShell
Next Post
SQL SERVER – System.IO.FileNotFoundException: Could not load file or assembly

Related Posts

15 Comments. Leave new

  • Nice example

    Reply
  • Hey I am having a similar issue and took the steps you suggested. I did not add the set xact_abort on code to the stored procedure but I did turn it on for the entire SQL Server (SSMS->SQL Server->Properties->Connections-Check the box for xact_abort). This is the error I am now receiving…any ideas?

    returned message “Cannot start more transactions on this session.”.

    Reply
  • enable the DTC in remote server (BigServer) or local server (SmallServer) or both?
    Is a reboot required?

    Reply
  • I’ve got a server 2003 R2 box talking to server 2012 and the dcomcnfg screen does nto have local DTC where do I add it?

    Reply
  • Tks! This solutions resolve my problem. kkkk great article.

    Reply
  • Thanks, Resolved my problem too..hola

    Reply
  • Thanks. Fixed my problem.

    Reply
  • Great post Pinal.
    Just keep reading and still with the doubt about which server did you alter the DTC.
    Can you please review the post, and express mention if the DTC setup is on BIGSERVER or SMALLSERVER.

    Reply
  • As per above step is followed but issue is not solved, please help us.
    INSERT into tmpLinkServer(SuffixAbbr,SuffixName)
    EXEC OPENDATASOURCE(‘SQLOLEDB’,’Data Source=xx.xx.xx.xx;User ID=id ;Password=pass’).DB.schema.SPName.

    Reply
  • New Error getting “No transaction is active”

    Reply
  • waiting for answer @pinalDave

    Reply
  • It’s was update. But it loacked the tables and it’s take too many time
    What can I do with it????

    Reply

Leave a Reply