cannot use PIVOT clause in my sql server 2005

Question

Hi,

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

Rajitha.

---

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 1

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

Answer 2

Hello Rahitha,

With a ALTER DATABASE command:

USE [master]
GO
ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 90
GO

Version 90 mean SQL Server 2005.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 3

The error description clearly means that the compatibility of the database in set to below the 2005 version, i.e. 2000.

PIVOTs are new to ver 2005, you need to set it up to 2005 version to run your PIVOT query.

---

~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011

Answer 4

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.
See http://msdn.microsoft.com/en-us/library/ms143359(v=sql.90).aspx

---

N 56°04'39.26"
E 12°55'05.63"