locked
Error handling, multiple error messages RRS feed

  • Question

  • Hi all.
    Have you noticed that sql server catches only last error message?

    Try to run this:
    exec('create table #t
    
      (
    
        t1 timestamp not null default 0
    
      )')
    
    

    You get:
    Msg 1755, Level 16, State 0, Line 1
    Defaults cannot be created on columns of data type timestamp. Table '#t', column 't1'.
    Msg 1750, Level 16, State 0, Line 1
    Could not create constraint. See previous errors.

    Now try to run the same thing in try catch:

    begin try
    
      exec('create table #t
    
        (
    
          t1 timestamp not null default 0
    
        )')
    
    end try
    
    begin catch
    
      declare @Error varchar(max)
    
      set @Error = error_message()
    
      raiserror(@Error, 16, 1)
    
    end catch
    

    The error message this time is:
    Msg 50000, Level 16, State 1, Line 10
    Could not create constraint. See previous errors.

    Does anyone have any better ideas how to handle sql server errors?

    Thanks

     

     

     

    Friday, September 10, 2010 9:30 AM

Answers

  • Does anyone have any better ideas how to handle sql server errors? 

    If your intention is to simply rethrow the original error(s) rather than actually handle them, perhaps TRY/CATCH isn't the right tool for the job.  You can simplly check @@ERROR after each statement and abort the script.  SEH provides value in that it is less verbose for more complex scripts but the trade-offs are that you can't report multiple errors from a single statement and cannot rethrow the original error(s) (1755 and 1750).
    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Kalman Toth Thursday, September 16, 2010 10:10 AM
    Friday, September 10, 2010 12:44 PM

All replies

  • Does anyone have any better ideas how to handle sql server errors? 

    If your intention is to simply rethrow the original error(s) rather than actually handle them, perhaps TRY/CATCH isn't the right tool for the job.  You can simplly check @@ERROR after each statement and abort the script.  SEH provides value in that it is less verbose for more complex scripts but the trade-offs are that you can't report multiple errors from a single statement and cannot rethrow the original error(s) (1755 and 1750).
    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Kalman Toth Thursday, September 16, 2010 10:10 AM
    Friday, September 10, 2010 12:44 PM
  • Try the links below. there is a nice artical on error handling.

    http://www.sommarskog.se/error-handling-I.html

    http://odetocode.com/Articles/79.aspx

    HTH

    • Marked as answer by Kalman Toth Thursday, September 16, 2010 10:10 AM
    • Unmarked as answer by Stimo Thursday, September 16, 2010 1:40 PM
    Friday, September 10, 2010 2:39 PM