Microsoft KB Archive/820200

From BetaArchive Wiki

Article ID: 820200

Article Last Modified on 10/27/2006


APPLIES TO

  • Microsoft Systems Management Server 2.0 Standard Edition
  • Microsoft Systems Management Server 2.0 Service Pack 4
  • Microsoft Systems Management Server 2.0 Service Pack 5



SYMPTOMS

After you apply the hotfix from Microsoft Knowledge Base article 303848 (Q303848.exe ) or the Systems Management Server 2.0 (SMS 2.0) Service Pack 4 (SP4) hotfix rollup package on an SMS 2.0 primary site server, the SMS 2.0 SMSPackages_del SQL trigger is removed from the SMSPackages table in the SQL database. The SMSPackages_del SQL trigger removes associated data from other, related SMS SQL tables when you delete a package by using the SMS 2.0 Administrator console. When you delete a package by using the SMS 2.0 Administrator console, orphaned data from the deleted package may remain in the SMS database.

Important The hotfix is included in the Service Pack 4 (SP4) hotfix rollup package. For additional information about the 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


CAUSE

During installation of the Q303848.exe hotfix or of the SMS 2.0 SP4 hotfix rollup package, a SQL script is executed to perform required updates to the SMS database. The script transfers the contents of the SMSPackages SQL table to a temporary table, and then the script deletes the SMSPackages SQL table.

When the SMSPackagesSQL table is deleted, the associated SMSPackages_del SQL trigger is also deleted. Additionally, when the temporary table is renamed to SMSPackages, the SMSPackages_del SQL trigger is not re-created.

RESOLUTION

To resolve this problem, re-create the deleted SMSPackages_del SQL trigger, and then remove any orphaned data that is left in the SMS database . You can also modify the SQL script (Update.sql) that is included with the hotfix to automatically create the SMSPackages_del SQL trigger for sites where the hotfix may not yet have been applied.

How to re-create the SMSPackage_del SQL trigger

Important Before you follow these steps, we strongly recommended that you back up the SMS database.

You must follow these steps on the SMS 2.0 site server computer that holds the primary site database where the hotfix files have been installed. This includes any site that was later upgraded to SMS 2.0 Service Pack 5 (SP5).

  1. On the primary site server or on a computer that has the SQL Server tools installed, click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.
  2. In the Connect to SQL Server dialog box, click the computer that holds the SMS 2.0 site database, click either the Windows authentication type or the SQL Server authentication type, and then click OK.
  3. In the SQL Query Analyzer console, make sure that the SMS 2.0 database is selected in the database selection box on the SQL Query Analyzer menu bar.

  4. Paste the following TSQL commands in the Query pane of SQL Query Analyzer:

    SET QUOTED_IDENTIFIER ON

if exists (select * from sysobjects where id = object_id('SMSPackages_del') and (type = 'TR'))
drop trigger SMSPackages_del
GO

CREATE TRIGGER SMSPackages_del ON SMSPackages FOR DELETE AS 
BEGIN 
delete ProgramOffers from ProgramOffers INNER JOIN deleted ON ProgramOffers.PkgID = deleted.PkgID 
delete PkgNotification from PkgNotification INNER JOIN deleted ON PkgNotification.PkgID = deleted.PkgID 
delete PkgAccess from PkgAccess INNER JOIN deleted ON PkgAccess.PkgID = deleted.PkgID 
delete PkgPrograms from PkgPrograms INNER JOIN deleted ON PkgPrograms.PkgID = deleted.PkgID 
delete PkgStatus from PkgStatus INNER JOIN deleted ON PkgStatus.ID=deleted.PkgID 
delete PkgRuleExpressions from PkgRuleExpressions INNER JOIN deleted ON PkgRuleExpressions.PackageKey=deleted.PkgID 
delete PkgServers from PkgServers INNER JOIN deleted ON PkgServers.PkgID=deleted.PkgID 
delete SecurityObjInstances from SecurityObjInstances INNER JOIN deleted ON SecurityObjInstances.InstanceKey=deleted.PkgID AND ObjectKey=2 
END 
GO

SET QUOTED_IDENTIFIER OFF


    Note Use SQL Query Analyzer to verify the command syntax. To do this, click Parse Query on the Query menu, or press CTRL+F5.

  5. After you have verified the TSQL command syntax, run the query. To do this, click Execute on the Query menu, or press F5.
  6. After the SMSPackages_delSQL trigger has been re-created, you can review it by opening the Object Browser in SQL Query Analyzer. To do this, follow these steps:
    1. If the Object Browser is not already displayed, click Tools on the SQL Query Analyzer menu, click Object Browser, and then click Show/Hide or press F8. This will display the Object Browser in the left pane of the SQL Query Analyzer console.
    2. In the Object Browser, expand the SMS 2.0 database.
    3. Expand the User Tables folder, and then locate and expand the dbo.SMSPackages table.
    4. Expand the Triggers folder, and then verify that the dbo.SMSPackages_del trigger now exists.
    5. Quit SQL Query Analyzer.

How to update the hotfix SQL script so that the SMSPackages_del trigger is re-created during later installations of the hotfix



If you plan to install the Q303848.exe hotfix or the SMS 2.0 SP4 hotfix rollup package to additional SMS 2.0 primary sites, you must update the SQL script (Update.sql) that is supplied with the SMS 2.0 SP4 hotfix rollup package to automatically re-create the dbo.SMSPackages_del trigger. To do this, follow these steps.

Important We strongly recommended that you test the Q303848.exe file hotfix or the SMS 2.0 SP4 hotfix rollup package on a lab site or on a test site before you apply the hotfix in a production environment. Back up the SMS database before you apply the SMS 2.0 SP4 hotfix rollup package.

  1. Extract the hotfix files to a temporary folder.
  2. In the temporary folder where you extracted the hotfix, expand the i386 folder, and then locate the Update.sql script.
  3. On the primary site server or on a computer that has the SQL Server tools installed, click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.
  4. In the Connect to SQL Server dialog box, click the computer that holds the SMS 2.0 site database, click either the Windows authentication type or SQL Server authentication type, and then click OK.

    Note You do not have to connect to an SMS database, because no specific SQL operations will occur. In the following steps, you use SQL Query Analyzer as an editing tool for the SQL script.
  5. In SQL Query Analyzer, click Open on the File menu. In the Open Query File dialog box, locate the Update.sql script in the temporary folder where the hotfix was extracted, and then click Open.

  6. In the Update.sql script, scroll to the bottom, and then locate the final line of the script. The final line contains the following statement:

    SET QUOTED_IDENTIFIER OFF

  7. Paste the following TSQL commands in the script immediately above the final line (SET QUOTED_IDENTIFIER OFF).

    Note The final line of the script is included in the following for clarification. The SET QUOTED_IDENTIFIER OFF statement must appear only one time at the end of the script.

    if exists (select * from sysobjects where id = object_id('SMSPackages_del') and (type = 'TR'))
drop trigger SMSPackages_del
GO

CREATE TRIGGER SMSPackages_del ON SMSPackages FOR DELETE AS 
BEGIN 
delete ProgramOffers from ProgramOffers INNER JOIN deleted ON ProgramOffers.PkgID = deleted.PkgID 
delete PkgNotification from PkgNotification INNER JOIN deleted ON PkgNotification.PkgID = deleted.PkgID 
delete PkgAccess from PkgAccess INNER JOIN deleted ON PkgAccess.PkgID = deleted.PkgID 
delete PkgPrograms from PkgPrograms INNER JOIN deleted ON PkgPrograms.PkgID = deleted.PkgID 
delete PkgStatus from PkgStatus INNER JOIN deleted ON PkgStatus.ID=deleted.PkgID 
delete PkgRuleExpressions from PkgRuleExpressions INNER JOIN deleted ON PkgRuleExpressions.PackageKey=deleted.PkgID 
delete PkgServers from PkgServers INNER JOIN deleted ON PkgServers.PkgID=deleted.PkgID 
delete SecurityObjInstances from SecurityObjInstances INNER JOIN deleted ON SecurityObjInstances.InstanceKey=deleted.PkgID AND ObjectKey=2 
END 
GO

SET QUOTED_IDENTIFIER OFF


    Note It is a good idea to verify the command syntax by using SQL Query Analyzer. To do this, click Parse Query on the Query menu, or press CTRL+F5.

  8. After the script has been updated, click Save on the File menu, and then click Save. Alternatively, you can save the file by pressing CTRL+S.

Note If you plan to apply the Q303848.exe hotfix or the SMS 2.0 SP4 hotfix rollup package to an SMS Primary site server that is based on the Alpha operating system, you must copy the updated Update.sql script from the hotfix i386 folder to the Alpha folder.

How to remove orphaned data from the SMS database

From the time when the Q303848.exe hotfix or the SMS 2.0 SP4 hotfix rollup package is first applied to the time when the SMSPackages_del trigger is re-created, any SMS packages that were deleted from the primary site may leave behind orphaned data in certain SQL tables in the SMS database. You must remove this orphaned data for each primary site that the Q303848.exe hotfix or the SMS 2.0 SP4 hotfix rollup package was applied to, even if the site was later upgraded to SMS 2.0 SP5. To do this, follow these steps.

Important Before you follow these steps, we strongly recommended that you back up the SMS database.

  1. On the primary site server or on a computer that has the SQL Server tools installed, click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer
  2. In the Connect to SQL Server dialog box, click the computer that holds the SMS 2.0 site database, click either the Windows authentication type or SQL Server authentication type and then click OK.
  3. In the SQL Query Analyzer console, make sure that the SMS 2.0 database is selected in the database selection box on the SQL Query Analyzer menu bar.

  4. In the Query pane, paste the following TSQL queries:

    Note The following queries only return data that has been orphaned.

    Select * From Pkgstatus Where ID Not In (Select Pkgid From SMSPackages)
Select * From PkgAccess Where PkgID Not In (Select Pkgid From SMSPackages)
Select * From SecurityObjInstances where InstanceKey Not In (Select Pkgid From SMSPackages)
Select * From PkgServers Where PkgID Not In (Select Pkgid From SMSPackages)
Select * From ProgramOffers where PkgID Not In (Select Pkgid From SMSPackages)
Select * From PkgRuleExpressions Where PackageKey Not In (Select Pkgid From SMSPackages)


    Note It is a good idea to verify the command syntax by using SQL Query Analyzer. To do this, click Parse Query on the Query menu, or press CTRL+F5.

  5. After you have verified the TSQL command syntax, run the query by clicking Execute on the Query menu or by pressing F5.

    Note After you execute the queries, if no rows were affected for one or more of the queries, no orphaned package data was found. In that case, you do not have to follow the remaining steps.

  6. If orphaned data is returned for one or more of the queries, remove the orphaned package data from the SMS database. To do this, paste the following TSQL queries in the Query pane of SQL Query Analyzer:

    Delete From Pkgstatus Where ID Not In (Select Pkgid From SMSPackages)
Delete From PkgAccess Where PkgID Not In (Select Pkgid From SMSPackages)
Delete From SecurityObjInstances where InstanceKey Not In (Select Pkgid From SMSPackages)
Delete From PkgServers Where PkgID Not In (Select Pkgid From SMSPackages)
Delete From ProgramOffers where PkgID Not In (Select Pkgid From SMSPackages)
Delete From PkgRuleExpressions Where PackageKey Not In (Select Pkgid From SMSPackages)
  7. Verify the command syntax by using SQL Query Analyzer. To do this, click Parse Query on the Query menu, or press CTRL+F5.

    After you have verified the TSQL command syntax, run the query by clicking Execute on the Query menu or by pressing F5.


Note You must follow these steps on all SMS 2.0 primary sites where the Q303848.exe hotfix or the SMS 2.0 SP4 hotfix rollup package was installed.

STATUS

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

Keywords: kbtshoot kbnofix kbprb kbbug KB820200



Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.


Retrieved from ‘https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/820200&oldid=195212’
the Creative Commons 3.0 ShareAlike (except the Microsoft KB Archive).
Powered by MediaWiki