:::: MENU ::::

change compatibility level of a SQL Server database

  • Aug 07 / 2008
  • 0
dbDigger, SSMS tips and tricks, T-SQL Scripts, Upgrade SQL Server

change compatibility level of a SQL Server database

I have a database with compatibility level 80 (upgraded from SQL Server 2000 to SQL Server 2005). Today when i tried to disable the trigger through following command

 Use DBNameHere  
 DISABLE TRIGGER triggerName  
 ON tablename  

Following error occurred

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword ‘TRIGGER’.

It was surprised as the command looked valid and used many times. It was unusual and i had no way but to disable it through SSMS for time being. Later i tried again in a different way and it worked through following command.

USE DBNameHere  
 ALTER TABLE tableName  

So if you ever get the above mentioned error on enable/disable command then it may be compatibility level issue. Because disable trigger is SQL Server 2005 command. As compatibility level 80 is of SQL Server 2000 so it will not work. If you do not have any problem then you may easily update compatibility level to 90 for upgraded databases.

To change compatibility level of a database to 90 (SQL Server 2005) use following script

ALTER DATABASE AdventureWorks  
 EXEC sp_dbcmptlevel AdventureWorks, 90;  
 ALTER DATABASE AdventureWorks  

Through SSMS you may change the compatibility level by right clicking the database and click properties. Following screen will appear and you may change the level there.

SQL Server dt properties

By the way compatibility levels of SQL Server databases are as followings

* 60 = SQL Server 6.0

* 65 = SQL Server 6.5

* 70 = SQL Server 7.0

* 80 = SQL Server 2000

* 90 = SQL Server 2005

Consult us to explore the Databases. Contact us