locked
SQL Server 2008 to 2000 Linked Server Issue RRS feed

  • Question

  • I have an issue where I recently applied SP1 CU8 to our standalone 64 bit instance of SQL Server 2008 Standard Ed.  Now, when executing queries to one of our 32 bit SQL Server 2000 servers the query simply hangs.  When I kill the spid on the 2008 machine it goes into an endless Rollback status.  When I kill the associated process on the remote server the process on the host server is still in a Rollback status.  Prior to applying the CU everything worked fine between these two servers.  I've tried re-creating the linked server connection and applying CU9 without success.  Would this be an instance of where using the Repair option from the installation cd would be a valid choice to fix whatever may be causing this?
    • Moved by Tom Phillips Monday, November 15, 2010 2:41 PM Possibly better answer from TSQL forum (From:SQL Server Database Engine)
    Monday, November 15, 2010 1:48 PM

Answers

  • I think we found the problem.  An index went missing.
    • Proposed as answer by Naomi N Thursday, December 2, 2010 3:12 PM
    • Marked as answer by Kalman Toth Wednesday, December 15, 2010 4:16 PM
    Thursday, December 2, 2010 2:59 PM

All replies

  • Hi,

    Could you please elaborate a bit? How did you execute query? How did you try to recreate the link server? Could you please post the error message?

    We can use the sp_addlinkedserver stored procedure to create a linked server, and then distributed queries can be run against this server. The following link describes how to create a linked server from SQL Server 2008 to SQL Server 2000, please see:

    http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/0782364d-dcb0-4c45-8343-5bfc4406ead4/

    For more information, please see:
    sp_addlinkedserver (Transact-SQL)

     

    Thanks,
    Ai-Hua Qiu


    Constant dropping wears away a stone.
    • Marked as answer by Ai-hua Qiu Wednesday, November 24, 2010 8:58 AM
    • Unmarked as answer by Ai-hua Qiu Wednesday, November 24, 2010 9:45 AM
    Wednesday, November 17, 2010 6:04 AM
  • Did you find an answer? I have the exact same problem that started after CU5 rollup patch was installed.

    You can start and stop the msdtc service on the server to kill the spid. Have not figured out how to make the query work tho.

    • Marked as answer by Ai-hua Qiu Wednesday, November 24, 2010 8:58 AM
    • Unmarked as answer by Ai-hua Qiu Thursday, December 2, 2010 12:55 AM
    Friday, November 19, 2010 2:56 AM
  • scanner2001:  Have not found a resolution yet.  Restarting msdtc service on local and remote server does not work. 

    Ai-Hua Qui:  Executing the query through both a query window as well as with a stored procedure within a job.  It's intermittent.  It works sometimes, but not all the time.  No error messages are given. 

    Wednesday, December 1, 2010 8:58 PM
  • I think we found the problem.  An index went missing.
    • Proposed as answer by Naomi N Thursday, December 2, 2010 3:12 PM
    • Marked as answer by Kalman Toth Wednesday, December 15, 2010 4:16 PM
    Thursday, December 2, 2010 2:59 PM