locked
RAISERROR in and database compatability level in SQL 2012 RRS feed

  • Question

  • First off, I'm a sysadmin and not much of a SQL programmer so take it easy on me :)


    We are trying to move an application which runs on SQL 2008 to SQL 2012 and have some issue with the fact that the raiserror syntax has changed in SQL 2012. I do have the database set to compatibility level 100 (2008) so I'm rather confused why this is an issue, isn't this exactly the kind of thing compatibility level is designed to address?

    So is this a bug, or is the compatibility level not really fully backwards compatible?

    P.S - This is a 3rd party application so modifying it isn't a practical option.

    Thursday, April 4, 2013 5:32 PM

Answers

  • Compatabiliy level has never meant complete backwards compatibility.  To quote BOL "Compatibility level provides only partial backward compatibility with earlier versions of SQL Server."   And you are correct.  Setting the compatibility does not allow you to use the Raiserror format without the () which was allowed in 2005 and 2008 even though the syntax defined in BOL said they were required.  I don't know of any way around this one except either change the application or don't upgrade to 2012.

    Tom


    • Edited by Tom Cooper Thursday, April 4, 2013 6:01 PM
    • Proposed as answer by Naomi N Thursday, April 4, 2013 6:42 PM
    • Marked as answer by Assaf Rahav Thursday, April 4, 2013 7:25 PM
    Thursday, April 4, 2013 6:00 PM
  • Just to support Tom's response there was a recent blog on this exact topic

    SQL Server Management: Compatibility Levels


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Assaf Rahav Thursday, April 4, 2013 7:25 PM
    Thursday, April 4, 2013 6:43 PM

All replies

  • What is the problem/error message you receive?  I'm not familiar with a difference between 2008 and 2012 as far as the format goes, the docs show that they are the same.

    http://msdn.microsoft.com/en-us/library/ms178592(v=sql.105).aspx

    2012

    RAISERROR ( { msg_id | msg_str | @local_variable }
        { ,severity ,state }
        [ ,argument [ ,...n ] ] )
        [ WITH option [ ,...n ] ]

    2008

    RAISERROR ( { msg_id | msg_str | @local_variable }
        { ,severity ,state }
        [ ,argument [ ,...n ] ] )
        [ WITH option [ ,...n ] ]


    Chuck Pedretti | Magenic – North Region | magenic.com

    Thursday, April 4, 2013 5:50 PM
  • Compatabiliy level has never meant complete backwards compatibility.  To quote BOL "Compatibility level provides only partial backward compatibility with earlier versions of SQL Server."   And you are correct.  Setting the compatibility does not allow you to use the Raiserror format without the () which was allowed in 2005 and 2008 even though the syntax defined in BOL said they were required.  I don't know of any way around this one except either change the application or don't upgrade to 2012.

    Tom


    • Edited by Tom Cooper Thursday, April 4, 2013 6:01 PM
    • Proposed as answer by Naomi N Thursday, April 4, 2013 6:42 PM
    • Marked as answer by Assaf Rahav Thursday, April 4, 2013 7:25 PM
    Thursday, April 4, 2013 6:00 PM
  • Just to support Tom's response there was a recent blog on this exact topic

    SQL Server Management: Compatibility Levels


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Assaf Rahav Thursday, April 4, 2013 7:25 PM
    Thursday, April 4, 2013 6:43 PM
  • Thanks, that does answer the question, and complicates my life a bit :)
    Thursday, April 4, 2013 7:27 PM
  • Thanks! Now back to the drawing board I guess.
    Thursday, April 4, 2013 7:29 PM
  • To make it more fun, there is a new command in SQL 2012, with almost the same syntax - but quite different behaviour.

    You can take those old RAISERROR commands and change them to ;THROW and add a ,1 at the end. Beware though that ;THROW aborts the batch on the spot - without rolling back any open transaction unless XACT_ABORT is ON.

    And please don't ask me what Microsoft has been smoking.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, April 4, 2013 9:56 PM
  • I ended up installing an SQL 2008R2 named instance on the same SQL server to support this one application. I was hoping to get everything over to 2012 but no luck.
    • Proposed as answer by jhersey Friday, August 23, 2013 2:05 PM
    • Unproposed as answer by jhersey Friday, August 23, 2013 2:05 PM
    Friday, April 5, 2013 9:45 PM
  • Backup your database in 2008, restore your database in 2012.  The trigger should restore just fine but will cause errors when the trigger is fired. 

    Right click on the Trigger in question, click Script Trigger as, Choose DROP and CREATE To, New Query Editor Window

    Look for the line with  RAISERROR XXXX 'the error text'.  Something like

    RAISERROR 99999 'The value entered is prohibited by this trigger.'

    Change the line to

    RAISERROR (99999, -1,-1, 'The value entered is prohibited by this trigger.')

    or

    RAISERROR (99999, 10, 1, 'The value entered is prohibited by this trigger.')

    If you have more than just a few triggers you could quickly script all the triggers out to a file.  Use notepad and search and manually fix them all or if you can write a little code. It wouldn't take long to write a quick app to insert the missing ( , -1,-1, )   See http://msdn.microsoft.com/en-us/library/ms178592.aspx 

     

     

     


     

     

    • Proposed as answer by KKeydel Saturday, March 25, 2017 3:47 PM
    Friday, August 23, 2013 2:44 PM
  • Backup your database in 2008, restore your database in 2012.  The trigger should restore just fine but will cause errors when the trigger is fired. 

    Right click on the Trigger in question, click Script Trigger as, Choose DROP and CREATE To, New Query Editor Window

    Look for the line with  RAISERROR XXXX 'the error text'.  Something like

    RAISERROR 99999 'The value entered is prohibited by this trigger.'

    Change the line to

    RAISERROR (99999, -1,-1, 'The value entered is prohibited by this trigger.')

    or

    RAISERROR (99999, 10, 1, 'The value entered is prohibited by this trigger.')


     

     


     

     

    I had the same issue with several stored procedures that were in a 2008 R2 database that was installed on a 2012 server.  The RAISERROR syntax has changed, despite what Microsoft says. The changes jhersey identifies here will fix the issue.
    Saturday, March 25, 2017 3:51 PM
  • I had the same issue with several stored procedures that were in a 2008 R2 database that was installed on a 2012 server.  The RAISERROR syntax has changed, despite what Microsoft says.

    Yes, it changed with SQL Server 6.0. That was long ago...

    Saturday, March 25, 2017 5:51 PM