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

Solarwinds
, ,
Previous Post
SQL SERVER – User Defined Functions (UDF) Limitations
Next Post
SQL SERVER – Fix : Error : Server: Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table

Related Posts

127 Comments. Leave new

  • Hi i have a similar problem. I an using sql server 2008. i make upgard scripts for a 2005ver. db. when i execute scripts like Declare @var int =0, they are executed on my environment but on client environment which has sql server 2005 installed, it throws error. i know how to correct this. but there any way to make settings of sql server 2008 make us know whether the script will run or not on sql server 2008.

    Reply
  • Andrew Osiname
    July 20, 2012 4:12 pm

    I changed my server to SQL 2005 from 2000 using EXEC sp_dbcmptlevel TQM, 90;

    It allowed me to use the PIVOT command but caused a strange problem.

    On some old stored procecures, a previous developer had written a query like so:

    SELECT
    s.firstname,
    c.carreg,
    .s.surname /*notice .s.surname*/

    FROM
    staff s
    INNER JOIN
    car c ON c.StaffID = s.StaffID

    I normally wouldnt write .s.surname because the table staff was aliased to s not .s

    However thats how the code was written.

    When i changed the compatibility level to 2005, the stored procedure didnt work anymore and when i changed it back to 2000 – it did…just thought it was worth mentioning.

    So i removed the extra .’s from all stored procedures and its all fine but a strange bug…

    Reply
  • Hi i have a similar problem. I am using sql server 2008. i make upgarde scripts for a 2005ver. db. when i execute scripts like Declare @var int =0; they are executed on my environment(sql server 2008) but on client environment which has sql server 2005 installed, it throws error. i know how to correct this. but there any way to make settings of sql server 2008 make us know whether the script will run or not on sql server 2005.

    Reply
  • –new syntax
    ALTER DATABASE test2008
    SET COMPATIBILITY_LEVEL = 100

    — Valid values of the database compatibility level are 90 (for 2005), 100 (for 2008), or 110 (for 2012).

    Reply
    • Hi Gleb

      Its not the Database compatiblity i need, Its the SQL SERVER Management studio compatiblity . when i run a script like
      DECLARE @var nvarchar(max) = ‘test data’

      On a DATABASE any compatiblity level , It does not give error if db is in sql studio 2008.
      but it gives error if the DB is on sql server management studio 2005.

      so i need a setting for my query window in sql server management studio 2008 to behave like sql server management studio 2005.

      Thanks
      Vipul

      Reply
  • Hi Gleb
    Its not the Database compatiblity i need, Its the SQL SERVER Management studio compatiblity . when i run a script like
    DECLARE @var nvarchar(max) = ‘test data’

    On a DATABASE any compatiblity level , It does not give error if db is in sql studio 2008.
    but it gives error if the DB is on sql server management studio 2005.

    so i need a setting for my query window in sql server management studio 2008 to behave like sql server management studio 2005.

    Thanks
    Vipul

    Reply
  • Pinal Sir,
    What is the significance of doing this. I am not able to get… Please reply me…

    Reply
  • Im using sql server 2005,the Database Compatible Level 90 is not supporting in vb.net , it supports 80 only.

    Reply
  • Jwalant Natvarlal Soneji
    April 17, 2013 4:06 am

    100 = SQL Server 2008
    110 = SQL Server 2012

    Reply
  • hello sir i developed a dotnet software with sql server 2005 as database,i want to install setup on the system which is not having sql server installed on that system.. . i am not able to do any database related operation with the setup because database is not there .. what is the solution for that? Please help me …
    [email removed]
    thank you sir

    Reply
  • Hi Sir I want to restore database of sql server 2008 to sql server 2005 with data created within it. How is it possible Please tell me…

    Reply
  • Hi:

    Maybe Is too late to make this answer but it’s necesary for me obtain some kind of information about HOW CAN I MIGRATE a sql v 7.0 database to sql V 2012, if you could help me with this issue I’ll be so congratulaed with you.

    Kind regards

    Reply
    • direct migration from SQL 7.0 to SQL 2012 is not possible. Microsoft supports two version upgrade so you need to migrate to intermediate version and then final.

      Reply
      • Abdullah Wasay
        July 8, 2015 9:51 pm

        Hi Pinal,
        Can you please share what are migration steps from SQL 2005 to SQL server 2008 R2 , from SQL server 2008 to SQL Server 2012 and so On . What are the known issues that we come across during migration

  • today i change the software package and also the databaseserver must be update but i cannot update this database error is database cannot read this all run in miscrosoft sql server 2005

    Reply
  • If COMPATIBILITY_LEVEL changed how we can trace it?

    Reply

Leave a Reply

Menu