Recently i´ve been working in an app with Microsoft SQL Server 2008.
One of the things the app must do is:
From one instance of SQL Server begin a distributed transaction and commit the transaction in another instance of SQL Server in other server.
According to microsoft, in both servers you must have the MSDTC service configured and started. I´m not going to deep in detail right now (but then i will).
I was happy beacause i had all in its place and from the source server i executed the next query:
but after executed the query i got an awful error:
OLE DB provider "SQLNCLI10" for linked server "" returned message "No transaction is active.".Msg 7391, Level 16, State 2, Line 5
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server " " was unable to begin a distributed transaction.
And that was the beginning of my headache. I was googling for some days and found nothing that could help me.
Before this happen all was good and worked well. The scenario before the error is next:
- Windows Server 2003 R2 (x86) Standard Edition
- SQL Server 2008 (x86) Standard Edition SP1
- Windows Server 2003 (x86) Enterprise Edition
- SQL Server 2000 Estandard Edition SP3
But IT team decided that the company needed new hardware and software(OS) for the servers.
So the scenario changed as follows:
- The same as before
I spent several hours trying to find a solution by googling and posting in forums, but nothing seems to take me to the solution.
Looks like in Windows Server 2008, the MSDTC has been improved and you have to be careful with the configuration.
So the problem was that i could not start a distributed transaction from a x64 bit Windows 2008 OS to a x86 bit Windows 2003 OS.
After waiting for replays in posts and trying several combinations of configurations nothing worked.
But finally i found a solution, so here it is:
First of all:
- Make sure both servers can ping each other by the machine name, cause MSDTC uses netBIOS to establish a connection.
- Open the command line on both servers and run this command
- On Server 1: ping Server2
- On Server 2: ping Server1
- If you got response from both servers you are good, else you need to check your network dns.
- Figure out if a firewall is active and blocking the MSDTC communication.
The second step is configure the MSDTC on both Servers and Here is how:
On Server 1 (Windows Server 2008)
1.- Open Start->Administrative Tools->Component Services.
3.- In the Local DTC properties clic in the security section and set the configuration as in the image
- No Authentication Required.
- Enable SNA LU 6.2 Transactions.
The final step is make shure the DTC service is started and configured to start when windows start.
1. Open Start->Administrative Tools->Services
Make sure the status is “Started” and the Startup Type is “Automatic”.
On Server 2 (Windows Server 2003)
To configure the DTC follow these steps
1. Open Start->Adminitrative Tools->Component Services
Now you need the verify that the service is started, i´m not going to detail this step as is the same in WS2008.
Just remember that all this configurations mus be done with an account that has the permissions to do it.
Linked Server Configuration on Server 1
Now the DTC is configured on both servers we must create and configure a linked server on Server 1 pointing Server 2.
Im not going to give much detail in how this is done, but here is a quick shot:
Here is my Linked Server in the SQL Server 2008 instance pointing to my SQL Server 2000 instance.
And remeber to apply this settings to the linked server:
Data Access: True
Enable Promotion of Distributed Transactions: True
You can do this when creating the linked server or when it already exists just right clic over it and select properties, then clic on the Server Options option in the left panel and change the options.
Testing Distributed Transactions
We’ve come so far so the final step is run the distributed transaction query of the beginning:
As you can see no error is raised.
If none of this stuff helps i recommend you to visit the next links for a deep troubleshooting of DTC:
Troubleshooting MSDTC Part 1
Troubleshooting MSDTC Part 2
Thanks for reading and hope this is very informative and useful for you.