Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002

    Unanswered: problem with distributed trans 2000 to 2005

    I have a test environment setup consisiting of one SQL Server 2000 32 bit instance on one box and one SQL Server 2005 64 bit edtion on another box. Nearly everything works fine but have found this one snag...

    Linked Server name: SQL2005

    From the SQL Server 2000 instance in Query Analyzer....

    -- this works fine
    Delete from [SQL2005].myDB.dbo.myTbl

    -- this just hangs
    Begin Tran
    Delete from [SQL2005].myDB.dbo.myTbl

    It doesn't make any difference what provider I use on the Linked Server.

    I have tried it on a SQL Server 2000 to SQL Server 2000 configuration and, interestingly, it would only work if I used set XACT_ABORT ON. However, in the SQL Server 2000 to 2005 configuration, this doesn't help.

    We have a number of DTS packages that do deletes across the linked server that are failing due to this issue so we're being stopped from continuing with the migration to SQL Server 2005.

    Any suggestions?


  2. #2
    Join Date
    Mar 2003
    Perhaps your DTC service is not running.

    I have these 2 related links:

    By the way, if you intend to migrate for an old SQL2000 DB to a new SQL2005 DB, why not to backup the old one and restore it on your SQL2005 server?

  3. #3
    Join Date
    Nov 2002
    The DTC service is runnnig on both servers.

    Actually, the problem isn't limited to Deletes within a distributed transaction... The problem is the distributed transaction itself. I repeated the test doing an insert across the servers. The insert works on its own but within a distributed transaction, it hangs.

    I have restored the database onto the SQL Server 2005 box to migrate them. I was refering to application DTS packages not working on the 2000 server because of the distributed transaction problem.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts