"No transaction is active.". Error with linked servers in SQL 2008 R2
Question
Tuesday, August 2, 2011 7:16 PM
I get the following message in our PeopleSoft environment when trying to update a table over a linked server connection using the PeopleSoft Application.
[Microsoft][SQL Server Native Client 10.0][SQL Server]OLE DB provider "SQLNCLI10" for linked server "SERVERANME" returned message "No transaction is active.".
[Microsoft][SQL Server Native Client 10.0][SQL Server]The operation could not be performed becaus
We are on SQL Server 2008 R2, running on Windows Server 2008 R2 Enterprise.
The PeopleSoft Environnment is connecting usingan ODBC with the following driver: Microsoft SQL Server Native Client Version 10.50.1600
In SQL Server Management Studio, I canrun the updates without problem using Domain users, and SQL Server users, all mapped to the same account as the PeopleSoft user.
We found this link for hotfixes to SQL 2005 and SQL 2008 versions, ( http://support.microsoft.com/kb/954950 ) but my understanding is that these fixed have been rolled into SQL 2008 R2.
Does anyone have any ideas how I can get past this?
Thanks
Chris
All replies (2)
Tuesday, August 2, 2011 11:05 PM ✅Answered
Turns, out the problem was with the Distributed Transactions Cooridnators on the two servers. Both servers came from the same VM template. The solution was posted in Kevin Feasel's blog: http://36chambers.wordpress.com/2011/04/08/msdtc-no-transaction-is-active/
"What happened in our case was that the servers were spun up from the same VMware template, so MS DTC had a conflict. Solving this was pretty easy. Run the following commands in the command prompt (as an Administrator!) for either the caller or the called server (or both):
net stop msdtc
msdtc -uninstall
msdtc -install
Then restart the server—not just the MSDTC service—and you’re good to go."
Thanks Kevin!
Tuesday, August 2, 2011 7:47 PM
I rant a DTSPing and had the following output:
++++++++++++++++++++++++++++++++++++++++++++++
DTCping 1.9 Report for SERVER_A
++++++++++++++++++++++++++++++++++++++++++++++
RPC server is ready
++++++++++++Validating Remote Computer Name++++++++++++
08-02, 14:41:37.077-->Start DTC connection test
Name Resolution:
SERVER_B-->xxx.xxx.xxx.xxx-->SERVER_B.ntc.edu
08-02, 14:41:37.117-->Start RPC test (SERVER_A-->SERVER_B)
Problem:fail to invoke remote RPC method
Error(0x6D9) at dtcping.cpp @303
-->RPC pinging exception
-->1753(There are no more endpoints available from the endpoint mapper.)
RPC test failed
I have sp_configure 'Ad Hoc Distributed Queries', 1 set on both servers and "rpc" and "rpc out" set to true on all servers.