Testing all procs before upgrading to Compatibility Level 90

  • Luiz-458831

    SSCommitted

    Points: 1904

    Comments posted to this topic are about the item Testing all procs before upgrading to Compatibility Level 90

  • GSquared

    SSC Guru

    Points: 260824

    Clever. I like it. This'll come in useful to me on the project I'm doing next week. Thanks.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • SQLRNNR

    SSC Guru

    Points: 281252

    Thx - this will prove useful in the new environment i just inherited.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • K Foster

    SSC-Addicted

    Points: 476

    I think I am missing something here... The database is compatibility level 80 and the script tries to create a proc in that database and expects it to trap the ones that error out because they aren't compatible with level 90. If compatibility level applies to the whole database then how can it error out on a proc that isn't being created on a level 90 database? I've got a stored procedure in a level 80 database that I believe should be incompatible in level 90 and above because no WITH keyword is with the locking hint, something like...

    Create procedure dbo.test_with

    as

    select col1, col2, col5 from dbo.table nolock

    go

    When I run the script it doesn't report the procedure as a problem.

    Either I don't understand something I need to do with the script or I don't understand the compatibility differences here

    http://msdn.microsoft.com/en-us/library/bb510680.aspx

    Can anyone enlighten me about this?

    Thanks.

  • K Foster

    SSC-Addicted

    Points: 476

    Since my last posting I've found an article that says that some of the table hints including nolock are exceptions to the 'WITH' keyword rule.

    Thanks.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply