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:
Press Enter. Once completed, type:
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.