DOCUMENT:Q304634 21-AUG-2001 [sms] TITLE :SMS: How to Set SQL Server 7.0 Compatibility Levels for SMS 2.0 PRODUCT :Microsoft Systems Management Server PROD/VER::2.0 OPER/SYS: KEYWORDS:kbenv kbtool kbsms200 ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - Microsoft Systems Management Server version 2.0 ------------------------------------------------------------------------------- SUMMARY ======= This article describes how to properly set the Microsoft SQL Server version 7.0 compatibility level for Systems Management Server (SMS) version 2.0. SQL Server 7.0 provides the option of running in a compatibility mode that emulates SQL Server 6.0 or 6.5 behavior. You can run Compatibility mode so that legacy programs can continue to run and interact with SQL as they did before the upgrade to SQL Server 7.0. Because SMS 2.0 is able to fully utilize the advanced features of SQL Server 7.0 and does not require legacy support, you should always run the SMS database in SQL Server 7.0 mode. If you run SQL Server in a compatibility mode that is lower than version 7.0, you cannot take advantage of certain SQL Server 7.0 features that are not enabled at lower compatibility levels. In addition, support for these legacy behaviors may not be included in future versions of SQL Server. When you set a SQL Server compatibility level, the setting affects the behaviors in the database you specify, not the entire SQL server. Because of this (even in the unlikely event that SMS 2.0 shares an SQL server with legacy programs that are running in an SQL 60 or 65 compatibility level), you should still run the SMS database in SQL 70 mode for maximum performance and compatibility. MORE INFORMATION ================ The compatibility level is specified for each database by using the sp_dbcmptlevel system stored procedure. Note that you can determine the current compatibility level of your database by running "sp_dbcmptlevel DATABASENAME" (without the quotation marks) in the SQL Query Analyzer tool. The database compatibility level can be set to 60 (version 6.0 compatibility), 65 (version 6.5 compatibility) and the default of 70 (version 7.0 compatibility). An upgrade from an earlier version of SQL Server to version 7.0 sets the compatibility level of existing user-defined databases to the version of SQL Server from which you are upgrading. For example, if your server was a version 6.5 server before you upgraded to SQL Server 7.0, the compatibility level for all your existing user-defined databases is set to 65. Additional query words: prodsms ====================================================================== Keywords : kbenv kbtool kbsms200 Technology : kbSMSSearch kbSMS200 Version : :2.0 Issue type : kbhowto ============================================================================= 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. Copyright Microsoft Corporation 2001.