SQL server 2008 Distributed transaction problem
Posted on 2009-05-19
Code executed on the SQL 2008 server (Installed on Windows 2008 OS)
Insert into Temp table
Exec [sql 2005 server].Dbname.dbo.Spname (SQL 2005 is installed on Windows 2003)
The Execute SP statement when executed alone runs fine, however when called with the Insert statement gives the following error
Server: Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "servername" was unable to begin a distributed transaction.
OLE DB provider "SQLNCLI10" for linked server "servername" returned message "No transaction is active.".
On both the servers the Ad Hoc Distributed Queries is enable @ sql level.
Windows level MS DTC remote service is running with no authentication on both the servers. The firewall is set to off. Also the linked server created has Data access, RPC in & RPC out set to true.