none
cannot use PIVOT clause in my sql server 2005

    Pergunta

  • 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

    segunda-feira, 16 de abril de 2012 04:20

Respostas

  • 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

    • Sugerido como Resposta pituach segunda-feira, 16 de abril de 2012 04:40
    • Marcado como Resposta Eshani Rao segunda-feira, 16 de abril de 2012 04:40
    segunda-feira, 16 de abril de 2012 04:35
  • 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

    • Marcado como Resposta Eshani Rao segunda-feira, 16 de abril de 2012 04:40
    segunda-feira, 16 de abril de 2012 04:36

Todas as Respostas

  • 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

    • Sugerido como Resposta pituach segunda-feira, 16 de abril de 2012 04:40
    • Marcado como Resposta Eshani Rao segunda-feira, 16 de abril de 2012 04:40
    segunda-feira, 16 de abril de 2012 04:35
  • 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

    • Marcado como Resposta Eshani Rao segunda-feira, 16 de abril de 2012 04:40
    segunda-feira, 16 de abril de 2012 04:36
  • 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

    segunda-feira, 16 de abril de 2012 04:37
  • 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"

    segunda-feira, 16 de abril de 2012 07:24