SQL Server Performance

PIVOT and compatibility

Discussion in 'SQL Server 2005 General Developer Questions' started by Madhivanan, Jun 18, 2007.

  1. Madhivanan Moderator

    When I tried executing PIVOT, I got this error

    Server: Msg 325, Level 15, State 1, Line 4
    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.

    So I tried setting it to 80 by using

    EXEC sp_dbcmptlevel 'mydb', 80

    Who am I Still getting the same error?

    Madhivanan

    Failing to plan is Planning to fail
  2. satya Moderator

    You need to change the compatibility level of the database to SQL Server 2005 otherwise the functionality is restricted to SQL Server 2000 - PIVOT command is not available for SQL Server 2000.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. Madhivanan Moderator

    Yes. Thats why I set it to 80 which is maximum level
    But still it throws the same error

    Madhivanan

    Failing to plan is Planning to fail
  4. satya Moderator

    80 stands for SQL 2000 and 90 is for SQL 2005.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  5. Madhivanan Moderator

    Thanks. Now it works [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  6. satya Moderator

    I think you are bumped by number on compatibility [<img src='/community/emoticons/emotion-1.gif' alt=':)' />].<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
  7. FrankKalis Moderator

    When you upgrade an existing SQL Server instance to SQL Server 2005 the database remain in 80 until you change that manuall. Got me stumped more than once.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
    Webmaster:http://www.insidesql.de
  8. satya Moderator

    .. true, I believe as "EXEC sp_dbcmptlevel 'mydb', 80" executed which is nothing but wrong values passed over here.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  9. Madhivanan Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />When you upgrade an existing SQL Server instance to SQL Server 2005 the database remain in 80 until you change that manuall. Got me stumped more than once.<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Yes it is [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

Share This Page