How to reapply SQL 2000 SP4 to update all the 13 scripts

Due to any issue, you may want to rerun SQL 2000 SP4 upgrade.

SQL 2000 Service Packs are cumulative. But when you re-run SP4 it only updates 7 Scripts those are new with SP4. This could be useful when SQL Server is already on SP3 and due to some reason the SP3 installation had not updated all the scripts/files. Now when you apply SP4 on a Server that is already on SP3, SQL Server 2000 SP4 upgrade only updates and applies the new scripts/files. Hence it run runs only 7 SQL Scripts.

In a specific scenario, we will be unable to configure replication. The error is "The user already exist in the distribution database". We have run the SP4 Upgrade, a couple of times.

The cause of the issue is that Due to some reason the SP3 installation had completed with errors and the sp_changedbowner procedure was not upgraded. The updated sp_changedbowner belongs to SP3. The only option is to dupe the SP4 setup as if the SQL Server was on RTM so that it would install/update all files and scripts.

Important: This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:
322756 How to back up and restore the registry in Windows

Then we will follow the below steps.

1. Make sure you have a backup of all your user databases.

2. Remove the below regkeys

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion\CSDVersion

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion\CSDVersionNumber

3. Install SP4 and it will upgrade successfully.

Levi Justus,
TL, Microsoft SQL Server