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
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.
–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).
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
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
Pinal Sir,
What is the significance of doing this. I am not able to get… Please reply me…
Im using sql server 2005,the Database Compatible Level 90 is not supporting in vb.net , it supports 80 only.
100 = SQL Server 2008
110 = SQL Server 2012
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
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…
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
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.
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
Potoe – What is the exact error ?
If COMPATIBILITY_LEVEL changed how we can trace it?
Profiler can help. You can also use DDL trigger.