SQL Server Performance Forum – Threads Archive
PIVOT and compatibility
When I tried executing PIVOT, I got this error Server: Msg 325, Level 15, State 1, Line 4Incorrect 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
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.
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
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.
Thanks. Now it works [<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
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>
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
.. 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.
<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
]]>