Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Distributed Transaction Issue Msg 7391

I solved an "interesting" issue today. It is about distributed transaction. When I set up transactional replication with immediate updating subscription between two SQL Server 2K5, using

EXEC sp_link_publication @publisher = 'Server1', @publication = 'PubUpdate1',@publisher_db = 'Pub_DB',@security_mode = 0,@login = 'sa',@password = 'sa_pwd'

on Server2, where the updatable subscribtion is located.

I keep on getting this error

OLE DB provider "SQLNCLI" for linked server "Server_2" 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 "Server_2" was unable to begin a distributed transaction

I went to MS support website and found this article:

How to troubleshoot error 7391 that occurs when you use a linked server in SQL Server
http://support.microsoft.com/kb/306212/en-us


But after reading and checking, it seems my environment configuration meets every requirement outlined in the article. After Googl'ed for long time, I still did not get any clue. After sending out an email to system admin group for help, I caught a hint from one of the system admin gurus, the issue is caused by the two servers (Server1 and Server2) are built from a same ghost image and thus they have the same MSDTC credentials. In Windows event log, I find the following message
The local MS DTC detected that the MS DTC on Server1 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 after msdtc -uninstall and then msdtc -install, I can successfully run the previous sp_link_publication.
It seems we cannot simply take KB article as a bible for trouble-shooting the exact issue in the article.:-)

Comments

Posted by mattheww on 21 July 2013

This article was a big help.  I experienced the same issue on a Win 2012 with SQL 2012 deployment - two servers, one as the publisher/distributor and hte other as a subscriber.  Everything was setup correctly and I followed the entire MSDN article about error 7391.  Still didn't work.  Then I read this article and realised I had built the first server (with MS DTC installed) and then cloned it for the second server.  Go figure.  This is a server build on Amazon AWS EC2 on-demand instances too (so the cloning was to create an AMI of the first server, then launch a new instance based on it).  Once I uninstalled and reinstalled the service, then rebooted, I was able to run the sp_link_publisher stored procedure on the subscription database to complete the link.  And it works (record updates on subscription DB make it back to publisher's DB).  So, there you have it, this issue with unique ID of the MS DTC service still exists, even years and 3 major Windows versions later.

Leave a Comment

Please register or log in to leave a comment.