Problem with INSERT INTO Temp Table on SQL 2000 using Linked Server to SQL 2005

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.