Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Unable to begin a Distributed Transaction
Published: May 05, 2014
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.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.