I am having Windows Server 2008 R2 at the server and SQL Server 2008 R2 and have created a linked server from this SQL Server to another server having SQL Server 2005 on it.
While using distributed transactions on the former one involving latter server in the stored procedure it is throwing an error:
OLE DB provider "SQLNCLI10" for linked server "slc0-onyx1" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Procedure sprocName, Line 53
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "laterDBName" was unable to begin a distributed transaction.
I have gone through many blogs and tried changing MSDTC settings but it didn't worked, I also tried to install Cumalitive Hot Fix # 441826 for this but of no use. Does anyone has any idea about this any help is appreciated.
It might be different configurations on distributed transactions. Please have a look at the Application log on Windows Logs on both servers if there is any error about it. For more information, please pay attention this article addressing the same error:
MSDTC: No Transaction Is Active.
TechNet Community Support
Marked as answer byStephanie LvThursday, April 05, 2012 9:38 AM