I have a SQL Server 2000 linked to a SQL Server 2005 and I am attempting populate a temporary table on the 2000 server using INSERT INTO by executing a stored procedure on the 2005 server. If I just execute the stored procedure, the data is returened with no issues. If I try to Insert the data into the temporary table the process hangs and times out.
This returns the data with no issues:
EXEC @.RetVal = MyLink.MyTable.dbo.spGetValue @.string1
This hangs and has to be killed or times out:
CREATE TABLE #TempTable (Value DECIMAL(19, 10) NULL)
INSERT INTO #TempTable
EXEC @.RetVal = MyLink.MyTable.dbo.spGetValue @.string1
SELECT * FROM #TempTable
I tried adding SET REMOTE_PROC_TRANSACTIONS OFF as suggested in an earlier post, but this had no effect.
You need to turn the MSDTC service on. You can this by clicking START > SETTINGS > CONTROL PANEL > ADMINISTRATIVE TOOLS > SERVICES. Find the service called 'Distributed Transaction Coordinator' and RIGHT CLICK (on it and select) > Start.
|||Thanks for your reply.
MSDTC service is already running on both servers.
