Welcome to MSDN Blogs Sign in | Join | Help

Mark Brown's Blog

Interesting facts about BizTalk, SharePoint, .Net, and more ...
Troubleshooting a MSDTC issue between Windows XP SP2/SP1 + SQL 2005 and Windows 2003 SP1 + SQL 2000

I was happily coding a solution for common problem of writing data in a distributed transaction fashion.   This was the first time I encountered the latest in security changes with service packs on workstation and server and the MSDTC.   Most of the information is available in Microsoft KnoledgeBase but I found a couple more symptoms that may be of interest.

Disclaimer

Please note that the following information may help resolve the problems found, however it introduces potential significant vulnerabilities and appropriate security measures should be taken and the items below adjusted to properly enforce security in a production or similar environment.

Common Causes

  • MSDTC on server 'ClientComputer' is unavailable
  • OLE DB provider "SQLNCLI" for linked server "RemoteServer" returned message "No transaction is active."
  • New transaction cannot enlist in specified transaction coordinator
  • Error 8004d00a. Distributed Transaction error
  • The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "RemoteServer" was unable to begin a distributed transaction
  • Server: Msg 7391, Level 16, State 1, Line 1 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to start a distributed transaction.

Steps to get working (this is not secure intended for information purposes only)

  1. Make sure MSDTC running on both machines
  2. Follow instructions found http://support.microsoft.com/?kbid=873160
  3. Although I did not have to reboot, some individuals on the internet required reboot to get the MSDTC settings to take affect

Related articles that may help...

How to troubleshoot MS DTC firewall issues
http://support.microsoft.com/default.aspx?scid=kb;en-us;306843

DTCPing troubleshooting tool
http://support.microsoft.com/default.aspx?scid=kb;en-us;306843

Original article that helped the most with configuration of MSDTC
http://support.microsoft.com/?kbid=873160

Vulnerabilities in MSDTC and COM+ Could Allow Remote Code Execution (902400)
http://www.microsoft.com/technet/security/Bulletin/MS05-051.mspx

Posted: Friday, December 30, 2005 11:48 AM by mab
Filed under:

Comments

Moshe said:

Let me just thank you, I had that problem with MSDTC and solved it by following the link you provided.

Thank you.

# November 2, 2006 7:36 AM

Steve said:

Thanks for the pointers with this. I was having huge problems until I read your post and followed the link to Microsoft.

# September 28, 2007 4:42 AM

jon said:

Hi,

This is a big comment.Thanks for your patience.

Update stmt. fails within a transaction over linked server. But this error occurs only one-way.

OLE DB provider “SQLNCLI” for linked server “B” returned message “No transaction is active.”.

Msg 7391, Level 16, State 2

The operation could not be performed because OLE DB provider “SQLNCLI” for linked server “B” was unable to begin a distributed transaction.

The following samples are run on Server A:

1./- ERROR (above)

begin tran

update B.dbmaint..abc

set x=5 where y = 4

commit tran

-/

2./-WORKS

update B.dbmaint..abc

set x=5 where y = 4

-/

3./-ERROR

begin distributed tran

select * from B.dbmaint..abc

commit tran

-/

4./-WORKS

begin tran

select * from B.dbmaint..abc

commit tran

-/

All 4 samples above work from server B

1./- WORKS

begin tran

update A.dbmaint..abc

set x=5 where y = 4

commit tran

-/

etc..

Server A:

Microsoft SQLServer 2005-9.00.3042.00(Intelx86)

Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Server B:

Microsoft SQLServer 2005-9.00.3042.00(Intelx86)

Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Thanks

# February 13, 2008 3:06 PM

SQL 2005 updating data in SQL 2000 DB via linked server returns error | keyongtech said:

PingBack from http://www.keyongtech.com/2150256-sql-2005-updating-data-in

# January 22, 2009 2:45 AM
Leave a Comment

(required) 

(required) 

(optional)

(required) 

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Page view tracker