Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
Database compatibilty level
From Wiki
Newer versions of the SQL Server database engine may not always be compatible with your database. To alleviate potential problems, you can set the compatibility level of a database to a previous version of the database engine. For example, you could be using a SQL2005 database engine with your database compatibility set to SQl2000 (compatibility level = 80).
The following list shows the compatibility value for each SQL Server database engine:
- 60 = SQL Server 6.0
- 65 = SQL Server 6.5
- 70 = SQL Server 7.0
- 80 = SQL Server 2000
- 90 = SQL Server 2005
- 100 = SQL Server 2008
The following query will list all of the databases in your SQL instance that is using an older compatibility level.
- SELECT Name, cmptlevel
- FROM master.dbo.sysdatabases
- WHERE cmptlevel != 10 * CONVERT(Int, CONVERT(FLOAT, CONVERT(VARCHAR(3), SERVERPROPERTY('productversion'))))
To change the compatibility level of a database, you must first put it in to single user mode, change the compatibility level, and then put it back in to multi user mode. Before doing this, it is important that you test all of the functionality in your database to make sure the higher compatibility level does not cause any of your existing functionality to break.
To change the compatibility level:
- ALTER DATABASE YourDatabaseName
- SET SINGLE_USER
- GO
- EXEC sp_dbcmptlevel YourDatabaseName, 90;
- GO
- ALTER DATABASE YourDatabaseName
- SET MULTI_USER
- GO
Microsoft is planning on deprecating the sp_dbcmptlevel system stored procedure. With SQL2008 and above, your should use the following code to alter the compatibility level of your database.
- ALTER DATABASE YourDatabaseName
- SET SINGLE_USER
- GO
- ALTER DATABASE YourDatabaseName
- SET COMPATIBILITY_LEVEL = 100;
- GO
- ALTER DATABASE YourDatabaseName
- SET MULTI_USER
- GO
--George Mastros 13:34, 29 April 2010 (GMT)
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.