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

16 thoughts on “SQL SERVER – 2005 – Change Compatibility Level – T-SQL Procedure

  1. Hello.
    On a 2005 sql restore basic sql 2000 that were compatible with 80, my question is that considerations or soft I can run to verify incompatibilities, and then switch compatibility 90 smoothly.
    Thank you

  2. @Paul.

    There is free in build tool Upgrade Advisor that comes with SQL Server, it could be downloaded as well from web.

    I suggest you run Upgrade Advisor on your database, it will list all issues that you could face after upgrade (changing compatibility from 2000 to 2005).

    I suggest not to do this on production databases, run this test, in your Test/Dev Env.

    ~ IM.

  3. Dear sir
    i have problem with this crosstab query .
    SELECT studentid,tablenumer,roomid,[SUB001] AS [ភាសារបរទេស(អគ្លេស)],[SUB002] AS វប្បធម៌ទូទៅ,[SUB003] AS ភាសាបាលី
    FROM(SELECT studentid,tablenumer,roomid,subjectid,score FROM tbl_studentexamein ) ps
    PIVOT(SUM(score)FOR subjectid IN([ភាសារបរទេស(អគ្លេស)],[វប្បធម៌ទូទៅ],[ភាសាបាលី])) as pvt

    _but it does not acept the selection of subjectid= SUB001,SUB002,SUB003 ON First select statment
    please help me to check this code how to run it with sqlserver 2005?

  4. I have changed the compatability level of sql 2005 to sql 2000

    ALTER DATABASE TestDB SET SINGLE_USER;
    EXEC sp_dbcmptlevel TestDB, 80;
    ALTER DATABASE TestDB SET MULTI_USER;

    Now when Iam trying to add “xml ” datatype it is getting added for a column which should not right?

    please specify what is the problem

  5. Hi,

    i have a production server with sql server express 2005 and need to move database on sql server 2008 workgroup edition.

    1.) just want to check can we move sql server express 2005 database to sql server 2008 workgroup edition server?
    2.) will there be any impact on the database due to same?

    Thanks & Rgds
    Deepak Bansal
    [email address removed]

  6. If I have no. of stored procedures in my database which are joined with an old style (*=, =*) for right or left joins, how can i convert them automatically instead of converting the each sp code manually.

    Is there anyway to do that ?

  7. Some databases on SQL 2008 R2 are in 90 compatibility mode, before I change it to 100, I would like to know what will break, so I ran Upgrade advisor for SQL 2008 R2 but it is giving bunch of errors like below:

    Method not found: ”System.Collections.Generic.IEnumerable`1 Microsoft.SqlServer.Management.SqlParser.SqlCodeDom.SqlCodeObject.get_Errors()”.

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #011 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s