SMS: SMS Collection Evaluator Errors Occur After You Install the Hotfix Rollup Package (327837)

SYMPTOMS

After you install the Systems Management Server (SMS) 2.0 Service Pack 4 (SP4) Hotfix Roll-up Package (HRP) on site servers that are using a SQL server that is configured to use either a case-sensitive sort order or a binary sort order, you receive many errors that relate to SMS Collections. For example, Collection Evaluator may log multiple "Error 620" status messages:

SMS_COLLECTION_EVALUATOR A00007 620 Microsoft SQL Server reported SQL message 207, severity 16: Invalid column name 'ChangeCount'

You cannot create new collections and you receive the following error message:

Unable to update parent collection due to SQL error. SQL Err>> #207 Sev 16: Invalid column name 'ChangeCount'. NEXT:SQL Err>> #10007 Sev 5: General SQL Server error: Check messages from the SQL Server. (null) ";

CAUSE

This problem may occur if either of the following conditions are true:

During the installation of the SMS 2.0 SP4 HRP, a SQL script is run to add a new column to the collections table and create a new stored procedure that updates the ChangeCount value. There is an error in this script that prevents it from running successfully on a SQL server that uses a case-sensitive sort order or a binary sort order. In this scenario, an error is reported in the Sql.log file that is created during installation of the HRP.For additional information about SMS 2.0 SP4 HRP, click the article number below to view the article in the Microsoft Knowledge Base:
323206 List of Bugs Fixed in Systems Management Server 2.0 SP4 HRP
During the installation of the SMS 2.0 SP4 HRP, the Q323206.exe installation script cannot locate the Isql.exe utility. Isql.exe is a utility that is included with Microsoft SQL Server, which you can use to run transact SQL commands against a SQL server database. If the utility cannot be found, important updates to the SMS database cannot be performed.

WORKAROUND

To work around this problem, view the Sql.log file that is produced in the SMS\Logs\Q323206 folder of the site server on which you installed the HRP. If the log file does not exist, the installation script may not have been able to locate the Isql.exe utility. If you view the Update.log file in the same folder and see an entry that states "Failed to find Isql.exe in the path or in the sql server bin directory," either make sure that the SQL Server administration tools are installed on the site server or manually run the Update.SQL script from the hotfix i386 folder against the SMS database on a computer that has the SQL Server administration tools installed.

If the Sql.log file does exist, view the log file for possible causes of this problem. The following data is a example of the Sql.log file output that is logged when this problem occurs because of the first scenario described in the "Cause" section of this article:

Invalid column name 'Name'.
Msg 207, Level 16, State 3, Server SERVERNAME, Line 12
Invalid column name 'Name'.
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25> 26> 27> 28> 29> 30> 31> 32> 33>
Msg 207, Level 16, State 1, Server SERVERNAME, Procedure sp_DecrementCollChangeCount, Line 5
Invalid column name 'ChangeCount'.
Msg 207, Level 16, State 3, Server SERVERNAME, Procedure sp_DecrementCollChangeCount, Line 5
Invalid column name 'ChangeCount'.
Msg 207, Level 16, State 1, Server SERVERNAME, Procedure sp_DecrementCollChangeCount, Line 6
Invalid column name 'ChangeCount'.
Msg 207, Level 16, State 3, Server SERVERNAME, Procedure sp_DecrementCollChangeCount, Line 6
Invalid column name 'ChangeCount'.

To work around this problem, follow these steps:

In the folder to which the HRP was expanded before the installation, go to the Platform folder (for example, I386), and then open the Update.sql file by using Notepad.
Search for the word Name to locate the following line in the script:
```
IF exists (SELECT Name
					
```
Replace the uppercase N in Name with a lowercase n, and then go to the following line:
```
WHERE Name = 'sp_DecrementCollChangeCount'
					
```

Replace the uppercase N in Name with a lowercase n.

The complete piece of this script now looks similar to the following script:

IF exists (SELECT name 
            FROM sysobjects 
            WHERE name = 'sp_DecrementCollChangeCount'
            AND type= 'P')
    DROP PROCEDURE sp_DecrementCollChangeCount
go

Save the changes that you made to the file, and then run the script against the SMS site server again.

To do so, start SQL Query Analyzer (or i/sqlw), select the SMS database, open the Update.sql script you just edited, and then run the query.

MORE INFORMATION

This problem may also occur if an incorrect user name and password combination is supplied to the SQL server when the SQL script part of the HRP installation is running. No interactive error checking is performed at this stage. When this problem occurs, the Sql.log file logs the following message:

Msg 18452, Level 14, State 1:
Login failed for user 'sa'. Reason: Not associated with a trusted
SQL Server connection.
DB-Library: Login incorrect.

To resolve this problem, use either of the following methods: