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
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.:-)