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

    Question

  • 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.

     

     

    Thursday, July 12, 2007 2:15 PM

All replies

  •  

    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.

    Thursday, July 12, 2007 3:07 PM
  • Thanks for your reply.

     

    MSDTC service is already running on both servers.

    Thursday, July 12, 2007 5:02 PM
  • Any luck with that problem? I have the same issue and the exact same configuration, sql2000 as linked server on 2005, hangs when executing sp from 2005 to insert code in 2000. Both servers have the MSDTC running. One thing to consider, the sql2005 server is on a virtual machine, and I created the linked server using the IP address for the sql2000 server.

    Thanks
    Tuesday, January 13, 2009 7:46 AM
  • Any further progress or a resolution to this issue?  I'm experiencing the same issue. 

    One further point I can add is that this only seems to be a problem when running the openquery() on servers that were built from the start as SQL 2005.  We have other 2005 servers that were upgraded from SQL 2000 that execute without issue.  All of our servers are still in 80 compatibility mode as well.

    Wednesday, July 22, 2009 7:46 PM