Reports that use the v_Add_Remove_Programs view stop responding and cause high CPU use in SMS 2003 SP3

Article translations Article translations
Article ID: 939872 - View products that this article applies to.
Expand all | Collapse all

On This Page

SYMPTOMS

After you upgrade to Microsoft Systems Management Server (SMS) 2003 Service Pack 3 (SP3), you run a report that is based on the v_Add_Remove_Programs view. However, the report stops responding, and the process uses 100 percent of the CPU resources.

For example, reports such as the "Computers with specific software registered in Add Remove Programs" report stop responding, and the process uses 100 percent of the CPU resources.

CAUSE

This problem occurs when 32-bit SMS 2003 SP3 advanced clients incorrectly report 32-bit data as 64-bit data. This problem occurs when the v_Add_Remove_Programs view in SMS 2003 SP3 joins data from the Add_Remove_Programs_DATA table together with data from the Add_Remove_Programs_DATA_64 table. Therefore, the same data is duplicated in both data tables.

RESOLUTION

Hotfix information

A supported hotfix is now available from Microsoft. However, it is intended to correct only the problem that is described in this article. Apply it only 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 2003 service pack that contains this hotfix.

To resolve this problem immediately, contact Microsoft Customer Support Services to obtain the hotfix. For a complete list of Microsoft Customer 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.

Prerequisites

To apply this hotfix, you must have SMS 2003 SP3 installed.

Restart requirement

You do not have to restart the computer after you apply this hotfix.

Hotfix replacement information

This hotfix does not replace a previously released hotfix.

File information

The English version of this hotfix has the file attributes (or later file attributes) 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 item in Control Panel.
Collapse this tableExpand this table
File nameFile versionFile sizeDateTimePlatform
Invcollectiontask.dll2.50.4253.310647,61601-Aug-200708:25x86
Sms2003ac-sp3-kb939872-x86.mspNot applicable340,99201-Aug-200708:25Not applicable

Hotfix installation information

Because of new architecture in SMS 2003, you must create a new package and a new program to advertise this hotfix to the advanced client computers. We recommend that you let the software installation wizard do this automatically. You have to create the package, the program, the collection, and the advertisement on the site at the top of the hierarchy.

After the hotfix is installed on an 32-bit SMS 2003 SP3 advanced client, the advanced client will no longer report data for the Win32Reg_AddRemovePrograms64 class during its next hardware inventory cycle. Therefore, the previously reported data will be removed from the associated table in the SMS database.

To apply this hotfix, follow these steps:
  1. Download the SMS2003-SP3-KB939872-X86-ENU.exe hotfix file.
  2. Copy the SMS2003-SP3-KB939872-X86-ENU.exe file to a folder on the SMS primary site server.
  3. On the SMS primary site server, click Start, point to All Programs, point to Systems Management Server, and then click SMS Administrator Console.
  4. Double-click Site Database.
  5. Right-click Collections, point to New, and then click Collection.
  6. In the Collection Properties dialog box, type Advanced Clients that need KB939872 in the Name box.
  7. Click the Membership Rules tab, and then click the yellow button.
  8. Type a name for the query, and then click Edit Query Statement.
  9. Click the General tab, and then click Show Query Language.
  10. In the Query statement section, type the following query, and then click OK three times:
    select SMS_R_System.Name, SMS_R_System.NetbiosName, SMS_G_System_SMS_ADVANCED_CLIENT_STATE.DisplayName, SMS_G_System_SMS_ADVANCED_CLIENT_STATE.Version from SMS_R_System inner join SMS_G_System_SMS_ADVANCED_CLIENT_STATE on SMS_G_System_SMS_ADVANCED_CLIENT_STATE.ResourceID = SMS_R_System.ResourceId where SMS_R_System.ClientType = 1 and SMS_G_System_SMS_ADVANCED_CLIENT_STATE.Name = "SmsInventory" and SMS_G_System_SMS_ADVANCED_CLIENT_STATE.Version < "2.50.4253.3106"
    Note To use this query, you must have at least one advanced client computer in your inventory to create the database classes. This query identifies advanced client computers that do not have this hotfix installed.
  11. On the SMS site server computer, double-click SMS2003-SP3-KB939872-X86-ENU.exe to start the installation wizard.
  12. Follow the steps in the installation wizard. After the installation files are copied, click Yes, I want to create a package and program now, and then click Next.
  13. Click Next to accept the default package name and the default program name.
  14. Click Next to accept the default package source location.
  15. On the details page, click Next, and then click Finish.
  16. In the SMS Administrator Console, double-click Packages.
  17. Right-click the KB939872 - Advanced Client Hotfix package, point to All Tasks, and then click Distribute Software.
  18. On the Welcome to the Distribute Software Wizard page, click Next.
  19. Click to select the check box that is next to the distribution points that you want to use, and then click Next three times.
  20. On the Advertisement Target page, click Advertise this program to an existing collection, and then click Browse.
  21. On the Browse Collection page, click Advanced Clients that need KB939872, and then click OK.
  22. Click Next two times.
  23. On the Advertise to Subcollections page, click Advertise the program only to members of the specified collection, and then click Next.
  24. Complete the Software Distribution Wizard.
To perform a silent installation of this hotfix, follow these steps.

Note Silent installations do not create the SMS package and programs. See the SMS Installation Directory\Logs\KB939872\ACReadme.txt file for instructions that you can use to manually update the SMS advanced clients.
  1. Click Start, click Run, type cmd, and then click OK.
  2. At the command prompt, switch to the folder that contains the SMS2003-SP3-KB939872-X86-ENU.exe file.
  3. At the command prompt, type SMS2003-SP3-KB939872-X86-ENU.exe /s, and then press ENTER.
To remove this hotfix, follow these steps on each SMS advanced client computer that has the hotfix applied.

Note You must have Windows Installer version 3.0 or a later version.
  1. Click Start, click Run, type cmd, and then click OK.
  2. At the command prompt, type the following command:
    msiexec /package ProductCode /uninstall PatchGUID /REINSTALL=All /REINSTALLMODE=maus
    In this step, replace ProductCode with the GUID of the SMS advanced client software that is installed on the client. Also, replace PatchGUID with the GUID for this specific SMS advanced client update.

    The product code for the English version of SMS 2003 SP3 advanced client is {4A39A27F-005B-407E-8CF5-F4D8065658E4}.The PatchGUID for the English version of SMS 2003 SP3 advanced client is {AB136A5F-6EB5-46A3-9283-13616362BC31}.
To verify the product code for the version of the SMS advanced client that you have installed, follow these steps:
  1. Paste the following text into Notepad:
    On Error Resume Next
    Dim installer : Set installer = Nothing
    Set installer = Wscript.CreateObject("WindowsInstaller.Installer") : CheckError
    Dim productName:productName = "SMS Advanced Client"
    Dim productCode, property, value, message
    For Each productCode In installer.Products : CheckError
    		If LCase(installer.ProductInfo(productCode, "ProductName")) = LCase(productName)  Then Exit For Next
    If IsEmpty(productCode) Then Wscript.Echo "Product is not registered: " & productName : Wscript.Quit 2
    WScript.Echo "ProductCode = " & productCode
  2. In Notepad, click File, click Save As.
  3. In the Save As dialog box, type GetACProdCode.vbs.
  4. At the command prompt of the SMS advanced client computer, type the following command:
    Cscript GetACProdCode.vbs

How to manually update the v_Add_Remove_Programs view

After you apply this hotfix, you can manually update the v_Add_Remove_Programs view to increase performance. To do this, run the following statement against the SMS database by using SQL Query Analyzer:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_Add_Remove_Programs]') and OBJECTPROPERTY(id, N'IsView') = 1)

drop view [dbo].[v_Add_Remove_Programs]

GO

 

SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO

 

CREATE VIEW dbo.v_Add_Remove_Programs

AS

SELECT     MachineID AS 'ResourceID', InstanceKey AS 'GroupID', RevisionID, AgentID, TimeKey AS 'TimeStamp', ProdID00 AS 'ProdID0', 

                      DisplayName00 AS 'DisplayName0', InstallDate00 AS 'InstallDate0', Publisher00 AS 'Publisher0', Version00 AS 'Version0'

FROM         dbo.Add_Remove_Programs_DATA

UNION ALL

(SELECT     arp64.MachineID, arp64.InstanceKey, arp64.RevisionID, arp64.AgentID, arp64.TimeKey, arp64.ProdID00, arp64.DisplayName00, arp64.InstallDate00, 

                        arp64.Publisher00, arp64.Version00

 FROM         Add_Remove_Programs_64_DATA arp64 LEFT JOIN

                        Add_Remove_Programs_DATA arp32 ON arp64.ProdID00 = arp32.ProdID00 AND arp64.MachineID = arp32.MachineID

 WHERE     arp32.ProdID00 IS NULL)

 

GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO
 
Grant select on v_Add_Remove_Programs to smsschm_users 

Go 

Grant select on v_Add_Remove_Programs to webreport_approle 

Go 

WORKAROUND

To work around this problem, use one of the following methods.

Method 1: Import the SMS 2003 SP2 report version

This method imports two report versions from SMS 2003 SP2. These report versions use an existing view that will not target the 64-bit Address Resolution Protocol (ARP) data.

To import these report versions, copy the text from step 1 into a .mof file. Then, import this file to the SMS 2003 SP3 site.

For identification, the two reports in this .mof file are renamed. "TEMP_SP2" is added to the beginning of the names of the reports. When the reports are renamed in this manner, the reports do not replace the existing SMS 2003 SP3 version of the reports. The data in these renamed reports does not include 64-bit programs that are listed in ARP.
  1. Copy the following text into a text editor such as Notepad. Then, save the file as a .mof file.
    // ********************************************************************************* 
    
    // 
    
    //                             Created by the SMS Export Object Wizard 
    
    // 
    
    //                             Created Friday, June 29, 2007 
    
    // 
    
    //                             File Name: Arp_reports.mof 
    
    // 
    
    // Comments : 
    
    // 
    
    // Export of two Add_Remove_Programs reports from a SMS 2003 SP2 site.  
    
    // For temporary use until post-SP3 issues with these same reports are 
    
    //  resolved.  Will not contain x64 Add_Remove Programs data. 
    
    // 
    
    // ********************************************************************************* 
    
    // ***** Class : SMS_Report ***** 
    
    [SecurityVerbs(140551)] 
    
    instance of SMS_Report 
    
    { 
    
                    Category = "Software - Companies and Products"; 
    
                    Comment = "Displays a summary of all computers that have specific software registered in Add Remove Programs"; 
    
                    DrillThroughColumns = {}; 
    
                    GraphXCol = 1; 
    
                    GraphYCol = 2; 
    
                    MachineDetail = FALSE; 
    
                    MachineSource = FALSE; 
    
                    Name = " <TEMP_SP2>  - Computers that have specific software registered in Add Remove Programs"; 
    
                    NumPrompts = 2; 
    
                    RefreshInterval = 0; 
    
                    ReportParams = { 
    
    instance of SMS_ReportParameter 
    
    { 
    
                    AllowEmpty = FALSE; 
    
                    DefaultValue = ""; 
    
                    PromptText = "Software Title"; 
    
                    SampleValueSQL = "begin \n if (@__filterwildcard = '') \n  Select DISTINCT DisplayName0 FROM v_GS_ADD_REMOVE_PROGRAMS order by DisplayName0 \n else \n  Select DISTINCT DisplayName0 FROM v_GS_ADD_REMOVE_PROGRAMS \n  WHERE DisplayName0 like @__filterwildcard \n  order by DisplayName0 \nend"; 
    
                    VariableName = "displayname"; 
    
    }, 
    
    instance of SMS_ReportParameter 
    
    { 
    
                    AllowEmpty = FALSE; 
    
                    DefaultValue = ""; 
    
                    PromptText = "Collection ID"; 
    
                    SampleValueSQL = "begin \n if (@__filterwildcard = '') \n  select CollectionID, Name from v_Collection order by Name \n else \n  select CollectionID, Name from v_Collection \n  WHERE CollectionID like @__filterwildcard \n  order by Name \nend"; 
    
                    VariableName = "CollID"; 
    
    }}; 
    
                    SecurityKey = ""; 
    
                    SQLQuery = "Select sys.Netbios_Name0, fcm.SiteCode,  sys.User_Domain0, sys.User_Name0, sys.Operating_System_Name_and0, arp.DisplayName0 \nFROM v_R_System sys \nJOIN v_GS_ADD_REMOVE_PROGRAMS arp ON sys.ResourceID = arp.ResourceID \nJOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID \nWHERE DisplayName0 = @displayname and fcm.CollectionID=@CollID"; 
    
                    StatusMessageDetailSource = FALSE; 
    
    }; 
    
    // ***** End ***** 
    
    // ***** Class : SMS_Report ***** 
    
    [SecurityVerbs(140551)] 
    
    instance of SMS_Report 
    
    { 
    
                    Category = "Software - Companies and Products"; 
    
                    Comment = "Displays a summary of the software installed on a specific computer that is registered in Add Remove Programs"; 
    
                    GraphCaption = ""; 
    
                    GraphXCol = 1; 
    
                    GraphYCol = 2; 
    
                    MachineDetail = TRUE; 
    
                    MachineSource = FALSE; 
    
                    Name = " <TEMP_SP2>  - Software registered in Add Remove Programs on a specific computer"; 
    
                    NumPrompts = 1; 
    
                    RefreshInterval = 0; 
    
                    ReportParams = { 
    
    instance of SMS_ReportParameter 
    
    { 
    
                    AllowEmpty = FALSE; 
    
                    DefaultValue = ""; 
    
                    PromptText = "Computer Name"; 
    
                    SampleValueSQL = "begin \n if (@__filterwildcard = '') \n  SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS WHERE SYS.Client0=1 ORDER By SYS.Netbios_Name0 \n else \n  SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS WHERE SYS.Client0=1 \n  and SYS.Netbios_Name0 like @__filterwildcard \n  ORDER By SYS.Netbios_Name0 \nend"; 
    
                    VariableName = "computername"; 
    
    }}; 
    
                    SecurityKey = ""; 
    
                    SQLQuery = "Select v_GS_ADD_REMOVE_PROGRAMS.DisplayName0, v_GS_ADD_REMOVE_PROGRAMS.Publisher0, v_GS_ADD_REMOVE_PROGRAMS.Version0 \nFROM v_GS_ADD_REMOVE_PROGRAMS \nJOIN  v_R_System ON v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceID \nWHERE v_R_System.Netbios_Name0 = @computername"; 
    
                    StatusMessageDetailSource = FALSE; 
    
    }; 
    
    // ***** End ***** 
    
  2. Import the reporting .mof file from the SMS administrative console. To do this, follow these steps:
    1. Open the SMS Administrator Console, and then expand the Reports node.
    2. Right-click the Reports node, click All Tasks, and then click Import Objects to start the Import Object Wizard.
    3. Click Next.
    4. Type the path of the .mof file that you saved in step 1, and then click Next.
    5. Click Next to confirm the name of the two reports that you want to import.
    6. In the Comments Review dialog box, click Next.
    7. Click Finish.
  3. Refresh the SMS Administrator Console to view and to use the two imported reports.

Method 2: Disable the reporting of the Win32Reg_AddRemovePrograms64 class

If there are no 64-bit clients in the environment, disable the reporting of the Win32Reg_AddRemovePrograms64 class in the Sms_def.mof file. Change the SMS_Report value for the class to FALSE. To do this, follow these steps:
  1. Make a backup copy of the Sms_def.mof file that is located on the SMS site server in the following folder:
    \SMS\Inboxes\Clifiles.src\Hinv
  2. Use any text editor, such as Notepad, to open the original Sms_def.mof file.
  3. In the file, locate the following section:
    [ SMS_Report     (TRUE),
      SMS_Group_Name ("Add Remove Programs (64)"),
      SMS_Class_ID   ("MICROSOFT|ADD_REMOVE_PROGRAMS_64|1.0"),
      Namespace      ("\\\\\\\\localhost\\\\root\\\\cimv2"),
      SMS_Context_1  ("__ProviderArchitecture=64|uint32"),
      SMS_Context_2  ("__RequiredArchitecture=true|boolean") ]
    
    class Win32Reg_AddRemovePrograms64 : SMS_Class_Template
    {
        [SMS_Report (TRUE), key ]
            string ProdID;
        [SMS_Report (TRUE)      ]
            string DisplayName;
        [SMS_Report (TRUE)      ]
            string InstallDate;
        [SMS_Report (TRUE)      ]
            string Publisher;
        [SMS_Report (TRUE)      ]
            string Version;
    };
    
  4. Use the following text to replace the text that is listed in step 3.

    Note The only change is in the first line of text.
    [ SMS_Report     (FALSE),
      SMS_Group_Name ("Add Remove Programs (64)"),
      SMS_Class_ID   ("MICROSOFT|ADD_REMOVE_PROGRAMS_64|1.0"),
      Namespace      ("\\\\\\\\localhost\\\\root\\\\cimv2"),
      SMS_Context_1  ("__ProviderArchitecture=64|uint32"),
      SMS_Context_2  ("__RequiredArchitecture=true|boolean") ]
    
    class Win32Reg_AddRemovePrograms64 : SMS_Class_Template
    {
        [SMS_Report (TRUE), key ]
            string ProdID;
        [SMS_Report (TRUE)      ]
            string DisplayName;
        [SMS_Report (TRUE)      ]
            string InstallDate;
        [SMS_Report (TRUE)      ]
            string Publisher;
        [SMS_Report (TRUE)      ]
            string Version;
    };
    
  5. Save the file to the following folder:
    \SMS\Inboxes\Clifiles.src\Hinv
    Use the file name "Sms_def.mof."
  6. Open the SMSDrive :\SMS\Logs\Dataldr.log file, and then look for text that resembles the following text:
    Warning: could not verify/remove hardware inventory data item record (DataItem id {3688ef6e-b9fe-4e45-be29-f5b211850c37}) during MOF class enumeration - 0x80004005
  7. In the SMS SQL Server database, run the following statement:
    Delete From DataItemContext where DataItemId = '{3688ef6e-b9fe-4e45-be29-f5b211850c37}'
    Note Replace the GUID by using the GUID that is found in the Dataldr.log file.

STATUS

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

MORE INFORMATION

For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:
824684 Description of the standard terminology that is used to describe Microsoft software updates

Properties

Article ID: 939872 - Last Review: July 1, 2008 - Revision: 4.0
APPLIES TO
  • Microsoft Systems Management Server 2003
Keywords: 
kbtshoot kbprb KB939872

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com