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

GO

DISABLE TRIGGER triggerName

ON tablename

GO

Following error occurred

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'TRIGGER'.

It was surprising 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

GO

ALTER TABLE tableName

DISABLE TRIGGER TriggerName

GO

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

SET SINGLE_USER

GO

EXEC sp_dbcmptlevel AdventureWorks, 90;

GO

ALTER DATABASE AdventureWorks

SET MULTI_USER

GO

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.

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