OLE DB provider "SQLNCLI" for linked server "X" returned message "No transaction is active". Msg 7391, Level 16, State 2, Line 2 The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "X" was unable to begin a distributed transaction.
Before Going to Fix that issue kindly know What is MSDTC? How to Install/Reinstall/know info
about MSDTC?
I've been talking for a while about MSDTC and transactions without explaining them. I know many of you reading this blog already know the terms, but some of the developers just entering the enterprise space don't know if they should care or not about these subjects.
MSDTC is an acronym for Microsoft Distributed Transaction Coordinator. As the name says, MSDTC is a Windows service providing transaction infrastructure for distributed systems. In this case, a transaction means a general way of structuring the interactions between autonomous agents in a distributed system. Each transaction is a state transformation with four key properties - the ACID properties: Atomic (all or nothing), Consistent (legal), Isolated (independent of concurrent transactions) and Durable (once it happens, it cannot be abrogated). There are different techniques that implement the ACID properties but the most known one is two-phase commit.
In other words, transaction processing is a software technology that makes distributed computing reliable. You can see a transaction as a unit of work in which a series of operations occur. The transaction (with its ACID properties) is providing protection in the case when one or more of these operations fail at any point in time. By using transactions, you can vastly simplify the error recovery for your system.
The main actors in a transaction are: the transaction manager (MSDTC), the initiator (the application which started the transaction) and the resource managers (the entities that manage data and work). The flow of actions in a simplified form is:
1. The client application (the initiator) begins a transaction by requesting one from the transaction manager;
2. The client app asks the resource managers to do work as part of the same transaction; during this step, the resource managers register with the transaction manager for that transaction ("they enlist");
3. The client app commits the transaction;
4. The transaction manager coordinates with the resource managers to ensure that all succeed to do the requested work or none of the work if done, thus maintaining the ACID properties.
Verify
To perform this procedure, you must have membership in Administrators, or you must have been delegated the appropriate authority.
To verify that the Microsoft Distributed Transaction Coordinator service (MSDTC) is running:
- Click Start, and then click Command Prompt.
- At the command prompt, type sc query msdtc, and then press ENTER.
- The system responds with information about the service. Confirm that these two items appear: SERVICE_NAME: msdtc and STATE : 4 RUNNING.
Reinstall MSDTC on windows 2008
Reinstall MSDTC
The system reported an unexpected error condition. You can resolve this condition by uninstalling and then reinstalling the Microsoft Distributed Transaction Coordinator service (MSDTC).
To perform this procedure, you must have membership in Administrators, or you must have been delegated the appropriate authority.
To uninstall and then reinstall MSDTC:
- Click Start, and then click Command Prompt.
- At the command prompt, type msdtc -uninstall, and then press ENTER.
- At the command prompt, type msdtc -install, and then press ENTER.
Disaster Recovery for MSDTC on Windows Server 2003 and 2008
You may experience serious problems with the Microsoft Distributed Transaction Coordinator (MSDTC) service. All attempts to troubleshoot MSDTC have not fixed the issue. You can follow these disaster recovery instructions on a standalone machine or a cluster node to reinstall the MSDTC service. For clusters these instructions will reinstall the local MSDTC service. After reinstalling MSDTC, you can reconfigure the MSDTC cluster resource using Cluster Administrator for Windows 2003 or Failover Cluster Management for Windows 2008.
During installation of MSDTC the default behavior for both Windows Server 2003 and 2008 is that if the registry key/hive exists, then it is left alone and not reverted to the clean-install state. If you are resetting MSDTC for disaster recovery purposes, we recommend deleting these hives to make sure the system will be reset to the clean install state. Otherwise you can leave these hives untouched to preserve user settings. Sometimes you need to reboot the machine after performing these steps to make it work.
To reinstall MSDTC on Windows 2003, perform the following steps:
- Stop the Distributed Transaction Coordinator service in the Services Control Panel
- At a command prompt run "msdtc -uninstall" without quotes. This removes the MSDTC service from the Services Control Panel and the associated service hive along with the CIDs from the registry:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSDTC
HKEY_CLASSES_ROOT\CID
Skip this step if you want to preserve existing configuration, such as network transactions and other MSDTC security settings. Otherwise
export the MSDTC Setup subkey:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\Setup and manually delete the following registry keys
if they exist:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSDTC
HKEY_CLASSES_ROOT\CID
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC
- At a command prompt run "msdtc -install" without quotes. This reinstalls the MSDTC service and the 3 registry hives above. Note the MSDTC Setup subkey is not populated on Windows 2003.
- Import the saved MSDTC Setup registry hive file (.reg) if you completed step 3 earlier:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\Setup
- At a command prompt run “msdtc –resetlog” without quotes
To reconfigure the MSDTC cluster resource after these steps on Windows Server 2003, please read these articles:
To reinstall MSDTC on Windows 2008, perform the following steps:
- Stop the Distributed Transaction Coordinator service in the Services Control Panel
- At a command prompt run "msdtc -uninstall" without quotes. This removes the MSDTC service from the Services Control Panel and the associated service hive along with the CIDs and CID.Locals from the registry:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSDTC
HKEY_CLASSES_ROOT\CID
HKEY_CLASSES_ROOT\CID.Local
- Skip this step if you want to preserve existing configuration, such as network transactions and other MSDTC security settings. Otherwise manually delete the following registry keys if they exist:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSDTC
HKEY_CLASSES_ROOT\CID
HKEY_CLASSES_ROOT\CID.Local
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC
- At a command prompt run "msdtc -install" without quotes. This reinstalls the MSDTC service and the 4 registry hives above.
- At a command prompt run “msdtc –resetlog” without quotes
To reconfigure the MSDTC cluster resource after these steps on Windows Server 2008, please read these articles:
Solution:
Ref:
http://www.theboreddba.com/Categories/issuesAndBugs/Unable-to-begin-a-Distributed-Transaction.aspx
This occured on a client's machine after they had migrated their SQL Server to new hardware with a fresh install of Windows Server 2008 R2. They attempted to use a linked server and received the following error message:
OLE DB provider "SQLNCLI" for linked server "X" returned message "No transaction is active".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "X" was unable to begin a distributed transaction.
The first thing to do here is to check the DTC (Distributed Transaction Coordinator) settings on your Windows server:
In Windows, click on START -> Admin Tools -> Component Services
This will open the following window:
Expand the left hand tree through Component Services, Computers, and My Computer. Then click on Distributed Transaction Coordinator.
You will then see an icon in the middle panel of the window which says "Local DTC":
Right click Local DTC and select Properties. You will then get a Properties Form. On this form, select the Security tab:
If “Network Access DTC” and all the other check boxes are not ticked as per the above, then tick them and press Okay.
You should get a warning box stating that Windows needs to restart the MSDTC service. Click yes (as this takes less than a second).
Once restarted, go back and check your original SQL query. It should now run successfully.
Caveat for a cloned machine
If you have created your server by using a clone of another machine and the above steps did not fix the issue (or maybe the Network Access DTC boxes were already ticked), then you could be looking at another problem entirely...
Open Event Viewer by clicking START -> Admin Tools -> Event Viewer:
Under Windows Logs -> Application you should be able to see an Error with teh MSDTC client:
Basically as you can see from the error, this was caused by cloning the machine and therefore causing a duplicate MSDTC on the network.
Luckily this is very easy to fix. Simply open a command prompt with Administrator Rights and type:
msdtc -uninstall
Press Enter. Once completed, type:
msdtc -install
Press Enter again. Go to Services and Start the MSDTC service:
And that should fix your problem. Try your SQL query again and see what happens.
But if you get again Get error as
ERROR: MSDTC on server 'ServerName' is unavailable.
Then you have to restart SQL Server Service kindly see below link for simple demo
see below for simple demo on article about above error
