Thursday, August 7, 2008

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

No comments:

Post a Comment

Any Comments: