Distributed Transaction Issue for Linked Server in SQL Server 2008

Question

I am running  W2K8, with SQL Server 2008. I am trying to execute one SP from one box to insert records in the table which lies on another box.

I am getting below error message.

"OLE DB provider "SQLNCLI10" for linked server "ASTDB05C\DDCHGRP2" 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 "ASTDB05C\DDCHGRP2" was unable to begin a distributed transaction."

 

I have followed each guideline mentioned in http://support.microsoft.com/default.aspx?scid=kb;en-us;306212.

Still I am getting above error. Firewall setting are also turned off for both the boxes.

Please advice.

 

Regards,

Milind

---

Mil's

Answer 1

Hi Milind,

If you are using SQL Server 2008 SP1, you might need to apply a lated Cumulative Update. The issue is first released in Cumulative Update 2. For more information: FIX: Error message when you run a distributed query in SQL Server 2005 or SQL Server 2008: "OLE DB provider 'SQLNCLI' for linked server '<Linked Server>' returned message 'No transaction is active'".

If the error can’t be removed, please refer to this thread addressing this issue in detail: Distributed Transactions fail on Linked server.

 

---

Best Regards,
Stephanie Lv

Answer 2

Hi Stephainie,

 

Thanks for the help. But actually we are using SQL server 2008 R2. My apologies that i wrongly mentioned SQL server 2008 instead of SQL server 2008 R2.

 

Can you please suggest fix for SQL server 2008 R2 ?

 

Regards,

Milind

---

Mil's

Answer 3

Hi Milind,

Have you taken a look at the similar thread provided above?

---

Best Regards,
Stephanie Lv

Answer 4

Hi Milind Shah,

 

I would recommend looking at you msdtc settings on both servers and using the DTCPing tool.

This will at least eliminate that it is related to msdtc. After that you can give us more info to help you.

 

Here is a guide for DTCPing: http://blogs.msdn.com/b/distributedservices/archive/2008/11/12/troubleshooting-msdtc-issues-with-the-dtcping-tool.aspx

 

Regards,

 

Cecil

Answer 5

Hi All,

I recently had the same issue, tried all the approaches described, but it didn't help. Because my task to solve this was not of the highest priority I forgot about it for some time.

Today I was troubleshooting some other issue and, by accident discovered source of that first problem.

In the EventViewer I found Error entry logged by MSDTC saying:

"The local MS DTC detected that the MS DTC on vm-server1 (other machine name that also runs DTC - my comment) has the same unique identity as the local MS DTC. This means that the two MS DTC will not be able to communicate with each other. This problem typically occurs if one of the systems were cloned using unsupported cloning tools. MS DTC requires that the systems be cloned using supported cloning tools such as SYSPREP. Running 'msdtc -uninstall' and then 'msdtc -install' from the command prompt will fix the problem. Note: Running 'msdtc -uninstall' will result in the system losing all MS DTC configuration information."

So I followed suggestion and run:

msdtc -uninstall
msdtc -install

This solved my problems with DTC. Hope it will help someone.

Piotr