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.
127 Comments. Leave new
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.
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?
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.
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???
Change the compatibility mode to 90
@madhivanan.
Unable to do so because I have only two options coming in i.e. 70 & 80. No option of 90.
Then it is not possible to use try/catch method
@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
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
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…
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… :)
Please!!! anyone can help me? why Do i not have level 90 on my sql server 2005? There’s only 7 and 8.
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
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
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
how to change version of database from 611 to 539
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
‘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
please help thats my code
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
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.
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…