Post reply

My Windows server has been renamed and the DTS packages in SQL server 2000 pointing to the old server and failed to execute

  • August 18, 2009 at 2:27 pm

    #100663

    Hi Folks,

    My Windows 2003 (standard edition) server has been renamed recently and the DTS packages in SQL server 2000 keeps pointing to the old server name and failed to execute.Since i have nearly 200 packages in the server, its tedious work for us to change each package one by one, Is there any option to change the connection in the DTS package to point to the new server name using simple steps.

    I will be very pleased if any one get me a solution for this.

    Thanks & Regards

    Babu

  • August 19, 2009 at 7:46 am

    #1041514

    Hi Babu,

    yes there is a way to programmatically do this. Have a look at the DTSPackage Object Library - the DTS.Package object has a collection property for connections.

    HTH 🙂

  • August 20, 2009 at 9:32 am

    #1042210

    here is a query I used for the same situation in SQL 2005:

    update msdb.dbo.sysdtspackages90

    set packagedata=

    CAST(

    replace(CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX))

    ,'OLD_SERVERNAME\INSTANCE,PORT#','NEW_SERVERNAME\INSTANCE')

    AS varbinary(MAX))

    where name like 'SOME_SUBSET_OF_PACKAGE_NAMES'

    I believe that it can be modified to work for SQL 2000

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply