I was attempting to write a PIVOT query in SQL Server 2005, when I received the following error:
Incorrect syntax near ‘PIVOT’. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.
The reason for this was that the database I was querying against was set to SQL Server 2000 compatibility mode, which I verified by running this query and seeing the result of ’80’:
select compatibility_level from sys.databases where name=db_name()
To work around this issue I used the database selection drop down menu in Management Studio to select a different database that was running in 2005 compatibility mode and ran the query from that database against the original database. To accomplish this, I had to modify the query to use a three part naming convention such as:
SELECT * FROM DatabaseNameHere.dbo.TableNameHere
The fact that new language features work against old databases in this way implies that the database from which you run the query has some hand in translating and possibly executing the query against the database with the lower compatibility level.
There are probably many hangups to using this work around for many scenarios. In my case I was just running some adhoc queries through Management Studio to perform some data profiling for an ETL project. If I were creating views or stored procedures I’m not sure how you would implement this workaround. Can you simply run the create statement from the 2005 database against the 2000 database such that it creates the stored procedure in the 2000 database, or would you need to create the stored procedure in the 2005 database? If you find you actually have to partition out these stored procedures and views into their own database, then make sure you document it well, and really weigh the cons of the complexity this would add to your project.
It is a fairly simple process to upgrade a database to a newer compatibility level, and the only reason I didn’t go this route was it would have only been for one query, and would have introduced significant risks in terms of the need for testing and verification that the change didn’t adversely effect front end applications.
If worse comes to worse, there are probably alternative ways you can write a query such that it will be compatible to the 2000 database.