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.

Database Compatible Level, SQL Scripts, SQL Stored Procedure
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

  • Are there any variables I can look at like DB size etc to estimate how long I can expect this query to execute? I am changing from 80 to 90, and it has now been executing for almost 10 minutes

    ALTER DATABASE mydb SET SINGLE_USER

    EXEC sp_dbcmptlevel mydb, 90
    GO

    ALTER DATABASE mydb SET MULTI_USER

    Reply
  • i upgraded sql server 2000 dev ed, to 2005 dev ed with default instance. after upgrade when iam changing
    compatibility of northwind database it showing below error. please can u tell the solution for this issue

    Msg 15416, Level 16, State 1, Procedure sp_dbcmptlevel, Line 92
    Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]

    Valid values of database compatibility level are 60, 65, 70, or 80.

    Regards,
    Kranthi

    Reply
  • I am using SQL Server 6.5. I want to migrate to SQL Server 2008. What are the best options available. Can I make scripts of Tables and Store Procedures and execute it in SQL Server 2008.

    Reply
  • Bengt Ericsson
    April 6, 2011 3:56 pm

    Hi, I have a question about 2005 compability level in SQL 2008. We have an application, Access – SQL, that work perfectly on both SQL 2005 and SQL 2008 but NOT on SQL 2008 in 2005 compability mode. Do you now why?

    Reply
  • Hi pinal,
    Can you tell meaning of each number sections of version in 9.0.1399.06 of sql server 2005 RTM as i only know that 9.0 stands for sql server 2005. so please tell me what 1399.06 stands for …?

    Regards,
    Vikas.

    Reply
  • I was trying to run TRY..CATCH block in SQL Server 2005 but it was giving me error. When I checked my version using @@VERSION it is showing “Microsoft SQL Server 2000 – 8.00.194”. But when I clicked on Help-> About-> it is showing 9.00.1399.00..Why So?? Am I using SQL Server 2000 database. If yes then how to change it to 2005 to use EXCEPTION HANDLING feature???

    Reply
  • @madhivanan.

    Unable to do so because I have only two options coming in i.e. 70 & 80. No option of 90.

    Reply
  • @vikas sahu

    Those number tells you about your edition and service pack details. Run below command and you’ll come to know about it-

    SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)

    For more details, please visit-
    https://support.microsoft.com/en-us/help/321185/how-to-determine-the-version-edition-and-update-level-of-sql-server-an

    Reply
  • Hi,

    I wonder if someone would be able to explain why the following 2005 code can run on DB with compatibility mode set to 80 (so 2000) on a SQL2005 server? Since xml is 2005 and doesn’t exist neither this code runs on SQL2000.

    declare @test xml
    set @test = ”

    Is this a “normal” behaviour SQL2005 cpat 80 mode or a feature?

    Is there any trick to really set a DB to full compatibility mode 80 (2000) instead of a mixed 2000-2005 mode? All this to make sure devs don’t write 2005 code against a 2000 DB which can be ported into a SQL2000 server. Thanks

    Regards,
    Miguel

    Reply
  • Please help me ….
    i am working on JOBBOSS software, with the backend of sql server 2005. where should i get the detailed information which is stored in the database for editing or modification…

    Reply
  • hi Pinal,
    Actually am shifting from sql 7.0 to sql server 2008. but now the problem arises when the formwe DB is restored in latter one.. i tried the sp_dbcmptlevel but in vain.. as 7.0 doed not support 80,90,or 100.. and i have also tried to first restore it to 2005 and executed the cmd and again restore to 2008 but this also not worked/…!!

    CAn u plz help by any possible solutionss…

    txs in advance… :)

    Reply
  • Please!!! anyone can help me? why Do i not have level 90 on my sql server 2005? There’s only 7 and 8.

    Reply
  • Hi Pinal,

    Can you please advise me to migrate MS SQL 2000 to another MS SQL 2000 server.. There is some hardware issues, actually we need to move SQL server to another server,, It is having around 95 DBs, so can you please tell me the best way to migrate the server?

    Please excuse me if I am posting this on the wrong area..

    Thanks you,
    Sujith

    Reply
  • Okay so this is funny.

    We have a procedure that uses MERGE statement and works fine in 2008.
    One of our clients uses 2005, and it was failing for them.

    On our current 2008 Servers, we set the compatibility level to 90 and even 80 later, (in hope of trapping the error because of the merge statement) but the merge procedure compiles and executed fine in each of the cases.

    I double checked the compatibility level is sys.databases, and opened up a brand new connection, set SIngle user prior to changing the compatibility level etc.

    I am stumped..

    What’s going on here ?
    Any ideas, insights ?

    Manu

    Reply
  • Hi Pinal, I took backup of a database on SQL 2008 R2 server after changing it’s compatibility level back to 90.

    Now I want to restore that DB on a SQL 2005 server, is it possible? I tried and it’s giving me error.

    Thanks,
    Vikas Verma

    Reply
  • how to change version of database from 611 to 539

    Reply
  • I would like to automate (via T-SQL) a backup and restore between a SQL Server 2008 (Production server) and SQL Server 2008 R2 (Report server). I know how to do it via the graphical interface, however I have hard time to figure out via T-SQL

    Thanks
    Martin

    Reply
  • ‘JONALD
    ‘when i want to update data,,..the error is “Fialed to Update”

    Private Sub btnok_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnok.Click

    Connection_open()

    cmd = New SqlCommand(” UPDATE TBLNAME SET FirstName = ‘” & txtfirstname.Text & “‘, MiddleName = ‘” & txtmiddlename.Text & “‘, LastName = ‘” & txtlastname.Text & “‘, Program = ‘” & maskedprogram.Text & “‘ where StudentID = ‘” & txtstudentID.Text & “‘ “, conn)

    j = cmd.ExecuteNonQuery

    If j > 0 Then

    MsgBox(“Update success!”)

    Else
    MsgBox(“Failed to update!”)

    End If
    connection_close()

    txtstudentID.Clear()
    txtfirstname.Clear()
    txtmiddlename.Clear()
    txtlastname.Clear()
    maskedprogram.Clear()
    txtstudentID.Focus()

    End Sub

    Reply
  • please help thats my code

    Reply
  • Hi, I am a business analyst working on SQL2000 to SQL2008R2 upgrade. We used Upgrade Advisor to identify the issues which require the code level changes. But how can i make sure if this were all the issues ?

    Is there anything which an Upgrade Advisor cannot catch ?

    Please help

    Reply

Leave a ReplyCancel reply

Exit mobile version