Tag Archives: Database Compatible Level

by

SQL SERVER – 2005 – Change Compatibility Level – T-SQL Procedure

Six months ago I wrote article about SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility. Yesterday I received an email asking that one of my blog reader is not able to use the sp_dbcmptlevel command with error that database is in use. He has asked me to write about proper procedure of changing database compatibility which will always work.

First read my previous article SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility as it has explained many details about compatibility.

The best practice to change the compatibility level of database is in following three steps.

  • Set the database to single user access mode by using ALTER DATABASE SET SINGLE_USER.
  • Change the compatibility level of the database.
  • Put the database in multiuser access mode by using ALTER DATABASE SET MULTI_USER.

Above three steps are translated in T-SQL in following three line script. Run this procedure in Query Editor.
ALTER DATABASE AdventureWorks
SET SINGLE_USER
GO
EXEC sp_dbcmptlevel AdventureWorks, 90;
GO
ALTER DATABASE AdventureWorks
SET MULTI_USER
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads
by

SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility – Part 2 – Management Studio

I have received quite a few request about post I have two days ago SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility, if this can be done using SQL Server Management Studio. It is very simple to do this using Management Studio as well but I still prefer T-SQL way.

Following steps will display the method to change the compatible levels.

Write click on database.
Click on Properties.
Click on Options.
Change the Compatibility level to desired compatibility. (See Attached image below)
Click OK.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

by

SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility

sp_dbcmptlevel
Sets certain database behaviors to be compatible with the specified version of SQL Server.

Example:
----SQL Server 2005 database compatible level to SQL Server 2000
EXEC sp_dbcmptlevel AdventureWorks, 80;
GO
----SQL Server 2000 database compatible level to SQL Server 2005
EXEC sp_dbcmptlevel AdventureWorks, 90;
GO

Version of SQL Server database can be one of the following:

  • 60 = SQL Server 6.0
  • 65 = SQL Server 6.5
  • 70 = SQL Server 7.0
  • 80 = SQL Server 2000
  • 90 = SQL Server 2005

The sp_dbcmptlevel stored procedure affects behaviors only for the specified database, not for the entire server. sp_dbcmptlevel provides only partial backward compatibility with earlier versions of SQL Server. A database containing an indexed view cannot be changed to a compatibility level lower than 80.

The best practice to change the compatibility level of database is in following three steps.

  • Set the database to single user access mode by using
    ALTER DATABASE SET SINGLE_USER
  • Change the compatibility level of the database.
  • Put the database in multiuser access mode by using
    ALTER DATABASE SET MULTI_USER

Reference : Pinal Dave (http://blog.SQLAuthority.com) , MSDN Article.