Monday, April 16, 2012 4:20 AM
I am trying to run a query which has PIVOT clause in it which is giving me 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.
How can i enable this in sql server 2005
Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you
Monday, April 16, 2012 4:35 AM
The PIVOT command is not available for SQL Server 2000. When we upgrade the SQL Server 2000 instance to SQL Server 2005, the database COMPATIBILITY_LEVEL remain in 80 until we change it manually.
You can change it as:
If you are running SQL 2005
EXEC sp_dbcmptlevel 'myDatabaseName', 90
Monday, April 16, 2012 4:36 AM
With a ALTER DATABASE command:
USE [master] GO ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 90 GO
Version 90 mean SQL Server 2005.
- Marked As Answer by Eshani Rao Monday, April 16, 2012 4:40 AM
Monday, April 16, 2012 4:37 AM
Monday, April 16, 2012 7:24 AM
Beware changing the compatibility level, without some serious thoughts.
There are some breaking changes in the Relational Engine that can break your application when you change the compatibility level.