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
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 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