Yesterday, my colleague experienced a problem when trying to execute SQL statements through a linked server. The query was fine if execute alone like
exec linkserver.northwind.dbo.GetOrders '12/31/2004'
However, when he executed it and try to insert the result into temp table
insert into #orders
exec linkserver.northwind.dbo.GetOrders '12/31/2004'
The server just hung without doing anything furthur. He tried to kill SPID but it waited in KILL/ROLLBACK state forever.
Cause:
When executing SQL statements which involve data from 2 servers in an implicit transaction like the second statement (where data is retrieved from one place to insert into another place), SQL Server need to establish DTC transaction to make sure the transaction is atomic. However, if the remote server has the default installation of Windows 2003 Server, Network DTC is disabled by default. This means that SQL Server cannot establish the transactions and will try forever to achieve that.
Solution:
Follow the instruction from http://www.microsoft.com/technet/prodtechnol/windowsserver2003/library/ServerHelp/e603c463-0636-4b85-8ada-c2b99f8555ac.mspx to enable Network DTC. If you have Windows Server 2003 SP1 installed, please also make sure that you open port for MSDTC to be connected remotely by either add MSDTC.exe to the allowed list or use the command line
netsh firewall set allowedprogram %windir%\system32\msdtc.exe MSDTC enable