January 2009 - Posts

Compatibility Level of a database

When users migrate from SQL Server 2000 to SQL Server 2005, not all databases are set to Compatibility Level 90 which is required to make use of the newly available functions

There are many methods to know the Compatibility Level of a database using queries

--Method 1
EXEC sp_dbcmptlevel your_database_name

--Method 2
EXEC sp_helpdb your_database_name

--Method 3
SELECT name,cmptlevel
FROM master..sysdatabases
WHERE name='your_database_name'

--Method 4
SELECT
name,compatibility_level
FROM sys.databases
WHERE name='your_database_name'

Also read http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/12/common-migration-issues-version-2000-to-2005.aspx