How to change the compatibility mode in SQL

---

Maybe sometimes you get errors on a query you just found on the internet like:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'apply'.

The problem is that you are using functions from a different SQL version, you need to change the compatibility mode in SQL.

Changing the compatibility mode will not bring the server, or the database, down. The issues primarily have to do with what object names are valid, and what new commands are allowed. If dev is working fine with compatibility level 90, and you wont' be running any different code or accessing any different objects on Live, there should be no problems.

In addition, changing the compatibility mode doesn't change the database in anyway, so you can always revert back to compatibility level 80 if you want.

To change to compatibility mode use the next code:

ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

or

EXEC sp_dbcmptlevel database_name, {80|90|100};

Version of SQL Server that can be reverted to 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

100 = SQL Server 2008

---

---