Knowledge Base

Primary Key Constraint Is Not Applied to the SMSPackages Table During Upgrade

Article ID: 819665

Article Last Modified on 7/24/2007

APPLIES TO

Microsoft Systems Management Server 2.0 Standard Edition

SYMPTOMS

When you upgrade Systems Management Server (SMS) 2.0 with Service Pack 5 (SP5) to SMS 2003, the Deployment Readiness Wizard may warn you about a failed test and recommend that you install the fix described in Q303848. The upgrade does not continue until the test is successfully completed.

CAUSE

This problem occurs because you have applied the hotfix Q303848 or the Hotfix Rollup Package (HRP) 4.1.

RESOLUTION

A supported hotfix is now available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing this specific problem. This hotfix might receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next service pack that contains this hotfix.

To resolve this problem, submit a request to Microsoft Online Customer Services to obtain the hotfix. To submit an online request to obtain the hotfix, visit the following Microsoft Web site:

http://go.microsoft.com/?linkid=6294451

Note If additional issues occur or any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. To create a separate service request, visit the following Microsoft Web site:

http://support.microsoft.com/contactus/?ws=support

The English version of this hotfix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.

   Date         Time   Version            Size    File name
   --------------------------------------------------------------
   10-Jun-2003  18:57  2.0.4.1           248,480  Sms2.0-kb819665-sp4-x86-enu.exe

After the hotfix is installed, these files will have the following file attributes or later:

   Date         Time   Version            Size    File name
   --------------------------------------------------------------
   25-Sep-2001  23:14  2.0.1250.7        762,688  Preinst.exe   (i386)   
   03-Jun-2003  20:10              3,144  update.sql

This hotfix searches for duplicates in the SMSPackages table, and then removes them. This action leaves only one entry per package ID. SMS 2003 Setup then applies the primary key constraint to the Package ID column of the SMSPackages table.

Note The application of a primary key constraint causes SQL to rebuild the indexes on the SMSPackages table. It may take a long time to rebuild the indexes on a large SMSPackages table. Therefore, Microsoft recommends that you apply this hotfix during non-usage hours.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section of this article.

MORE INFORMATION

The Q303848 hotfix deletes and recreates the SMSPackages table but does not reapply the primary key constraint on the Package ID column. Without the primary key constraint, duplicate Package IDs may exist in the SMS database. Duplicate Package IDS prevent SMS 2003 Setup from applying the primary key constraint during the upgrade. Hotfix Q819665 corrects this problem.

Additionally, the Q303848 hotfix included in SMS SP5 and the SP4 HRP removes the SMSPackages_Del SQL trigger from the SMSPackages table. When Packages are deleted from the Packages node in the SMS Administrator console, orphaned data remains in the SMS database. During installation of the Hotfix Rollup Package (HRP) or SP5, an SQL script runs to apply the required updates to the SMS database. Among other tasks, the script deletes the SMSPackages table after its contents are transferred to a temporary table. When the script deletes the SMSPackages table, it removes its associated SQL trigger (SMSPackages_Del). When the temporary table is later renamed to SMSPackages, the trigger is not re-created. When a package is deleted, the SMSPackages_Del trigger runs to remove associated package data from other SMS SQL tables. The delete trigger cleans the following tables when you delete a package:

ProgramOffers
PkgNotification
PkgAccess
PkgPrograms
PkgStatus
PkgRuleExpressions
PkgServers
SecurityObjInstances

Hotfix Q819665 is an SQL script that searches for and removes any rows that contain duplicate package IDs. If it finds duplicate rows, the SQL script deletes all but one instance. The script assumes that the rows are completely duplicated. For example, if the script duplicates the Package ID, but another attribute such as Package Name is different, the script detects that as a duplicate row and deletes the package.

This script also recreates the delete trigger and removes any orphaned information from the package tables. For additional information about the Q303848 hotfix, click the following article number to view the article in the Microsoft Knowledge Base:

303848 SMS: You Cannot Type a Share Name That is Longer Than 64 Characters When You Create Packages

Keywords: kbqfe kbhotfixserver kbsms200presp6fix kbupgrade kbfix kbbug KB819665

Microsoft KB Archive/819665

Contents