SMS: Upgrading SQL Server Used for an SMS Database

 Last reviewed: April 24, 1997
Article ID: Q149178
The information in this article applies to:
  • Microsoft Systems Management Server versions 1.0 and 1.1
  • Microsoft SQL Server versions 4.21a and 6.0

SUMMARY

Microsoft Systems Management Server (SMS) uses a SQL Server database to store its system information and computer inventory. When you upgrade the SQL Server containing the database for an SMS version 1.0 or 1.1 site, additional procedures are required to re-create some of the stored procedures for the SMS database.

MORE INFORMATION

If the stored procedures are not restored, the SMS Scheduler will not be able to activate jobs that use the Include Subsites option (jobs will remain in a Pending state and will never be activated).

To re-create the stored procedures for an SMS database:

  1. Run the SQL script Sp_site.sql against the SQL Server containing the SMS database. When you run the script, make sure the SMS database is the active database. The Sp_site.sql script is located in the <Smsdrive>:\<Smsdirectory>\<Site>.srv\<Platform>.bin directory of the SMS site server.

  2. You can use ISQL/W to run the script file. To use ISQL/W to run the script, start ISQL/W, connect to the SQL Server containing the SMS database, select the SMS database in the DB box, open the Sp_site.sql script located on the site server, and run the script. The script has run successfully if only the following text is returned in the Results tab: 

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

  3. If the Results tab displays Invalid Object Name errors, you have run the script against a non-SMS database.

Note: You do not need to manually run the Sp_site.sql script for SMS 1.2 databases.

Additional query words: prodsms prodsql
Keywords : kbother smsgeneral smshowto
Version : 1.0 1.1 1.2 4.21a 6.0
Platform : WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 24, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.