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


The information in this article applies to:

  • Microsoft Systems Management Server 2.0

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, but it is only intended to correct the problem that is described in this article. Only apply it to systems that are experiencing this specific problem. This hotfix may receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next SMS 2.0 service pack that contains this hotfix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the hotfix. For a complete list of Microsoft Product Support Services telephone numbers and information about support costs, visit the following Microsoft Web site:

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

Note In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The usual support costs will apply to additional support questions and issues that do not qualify for the specific update in question.

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

Modification Type:MinorLast Reviewed:4/14/2006
Keywords:kbQFE kbHotfixServer kbSMS200preSP6fix kbUpgrade kbfix kbBug KB819665 kbAudITPRO
©2004 Microsoft Corporation. All rights reserved.