Upgrading a Microsoft SQL Server for SMS Databases

SMS uses SQL Server to store its system information and computer inventory data. When you upgrade SQL Server for SMS 1.0 or 1.1 databases, additional steps are required to re-create some of the stored procedures used by SMS. If these stored procedures are not created, then the SMS Scheduler cannot activate jobs that use the Include Substitutes option. This means that jobs will remain in a pending state and will not be activated.

    To re-create the stored procedures for an SMS database

1. From the directory of the SMS site server on the drive that SMS was installed on, run the SQL script sp_site.sql against the SQL Server containing the SMS database. The sp_site.sql script is located in C:\SMSDIRECTORY\SITE.SRV\PLATFORM.BIN.

You can use ISQL/w in the SQL Server program group to execute the script file.

    To use ISQL/w to execute the script file

1. From the SQL Server 6.5 program group, click the ISQL/w icon.

   The Microsoft ISQL/w window opens and displays the Connect Server dialog box.

2. Connect to the SQL Server that contains the SMS database.
3. In the DB box, enter the SMS database.
4. To open the sp_site.sql script located on the site server, click the Load SQL Script button and select the script.
5. From the Query menu, choose Execute to run the script. The script returns the following text if it is sucessful:

   This command did not return data, and it did not return any rows.
     

   If the Results tab displays 'Invalid Object Name' errors, verify that you did not execute the script in the wrong database.

   Note that you do not need to manually run the sp_site.sql script for SMS version 1.2 databases. For more information, see the Microsoft Systems Management Server documentation.