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 (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Reclaim Space After Dropping Variable – Length Columns Using DBCC CLEANTABLE
Next Post
SQL SERVER – Time Out Due to Executing DELETE on Large RecordSet

Related Posts

15 Comments. Leave new

  • thanks

    Reply
  • 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

    Reply
  • bravo Dave!!

    Reply
  • Hi.
    I want to wnow that now can i set default user when i create a new database.

    Ajay

    Reply
  • Hi.
    I want to know that now can i set default user when i create a new database.

    Ajay

    Reply
  • Will there be any problems changing the compatibility level from 2000 to 2005?

    Reply
  • @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.

    Reply
  • 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?

    Reply
  • In SQL 2008 we can do it using Alter command

    ALTER DATABASE database_name
    SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

    Reply
  • 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

    Reply
  • Deepak Bansal
    July 28, 2011 12:17 am

    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]

    Reply
  • 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 ?

    Reply
  • 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()”.

    Reply

Leave a Reply