Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6

    Unanswered: CTEs but not PIVOT, huh?

    Meant to post this in the SQL Server Forum.

    I just noticed one of the databases is in 80 compatibility mode on a 2005 instance today when I tried to use the PIVOT operator.

    However I wrote some procs in the same DB using Common Table Expressions and those procs are working.

    they are both T-SQL enhancements in 2005. How come one works and the others does not?

    I do not really care what the answer is. I can rewrite my proc. I just felt like ranting.
    Last edited by Thrasymachus; 10-27-08 at 16:14.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    moved to SQL Server forum

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Since CTE's are basically temp tables, it may be that they are treated as being in the tempdb database. Still, I would get the database upgraded as soon as possible.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I sent the appropriate manager and DBA a note. The DBA wrote back asking what the downsides are and how this happened. I wanted to tell her I am just a traveler passing through, but someone just probably attached the MDF and did not follow through and then listed some of the enhancements in 2005.

    Its going to be ugly to recode. Stupid crosstab with dynamic columns. there are plenty examples involving ugly code in SQL 2000 on the interwebs.

    what gets me is that the DBA did not even know and this is her only production server.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The real WTF is why you would be do dynamic pivots in the database in the first place.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    being lazy.

    my reporting services skills are minimal, so I lately when I have to do RS, I do nearly everything in SQL and I return a final result to RS to display. I am sure there is a better way in RS.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    The real WTF is why you would be do dynamic pivots in the database in the first place.
    TRWTF is you didn't even get the acronym right.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Thrasymachus
    being lazy.

    my reporting services skills are minimal, so I lately when I have to do RS, I do nearly everything in SQL and I return a final result to RS to display. I am sure there is a better way in RS.
    I'm no RS expert but it should be a nice, simple Matrix. Easier than dynamic pivots I promise.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    that would involve learning stuff. I got sample code in SQL.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by pootle flump
    TRWTF is you didn't even get the acronym right.
    WTF???????
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •