SQL Server Error Severity and .NET

I actually wrote this almost a year ago but forgot to post it.

The other day I noticed some oddness in the Messages window of SQL Server Management Studio when using RAISERROR with different error severities. Running the following:

PRINT 'Start.'
RAISERROR (N'Error.', 16, 1)
PRINT 'End.'

produces...

Start.
Msg 50000, Level 16, State 1, Line 2
Error.
End.

However if you increase the severity to 17 or 18 you get this...

Start.
End.
Msg 50000, Level 18, State 1, Line 2
Error.

Odd as the error message has now moved from between the "Start" and "End" to after the "End". Wondering what the significance of the change from severity 16 to 17 was and why Management Studio should treat them differently I headed over to SQL Server Books Online which says:

• 0-10 are informational messages
• 11-16 are errors that can be corrected by the user
• 17-19 are application errors that the user can't correct
• 20 and above are fatal errors

So there is a difference but that still doesn't explain Management Studio's behaviour. Management Studio uses the .NET SqlClient for running queries so a brief look at the docs shows the SqlConnection class has a FireInfoMessageEventOnUserErrors property which, when set to true, reports any errors of severity less than 17 to the InfoMessage handler rather than throwing a SqlException.

I've put together a quick Snippet Compiler script to test this out which you can download here. The script has a connection string at the top which is looking for a local SQL Express instance with integrated security by default so you may need to amend this.

Results

Queries 3 and 4

These two queries show the differences observed in Management Studio quite nicely with the InfoMessage handler being fired for the severity 16 error but a SqlException being thrown for the severity 18.

-------------------------------------
Executing Query 3:


PRINT 'Start.'
RAISERROR (N'Error.', 16, 1)
PRINT 'End.'


Messages:

Info message fired: Start.
Info message fired: Error.
Info message fired: End.


Result:
Success.
-------------------------------------
Executing Query 4:


PRINT 'Start.'
RAISERROR (N'Error.', 18, 1)
PRINT 'End.'


Messages:

Info message fired: Start.
Info message fired: End.


Result:
SqlException.
Error severity: 18
Message: Error.
-------------------------------------

Query 5

Query 5 causing a severity 20 error (a fatal error) displays some slightly different behaviour as it both fires the InfoMessage handler and throws a SqlException.

-------------------------------------
Executing Query 5:


PRINT 'Start.'
RAISERROR (N'Error.', 20, 1) WITH LOG
PRINT 'End.'


Messages:

Info message fired: Start.
Info message fired: Process ID 51 has raised user error 50000, severity 20. SQL
Server is terminating this process.


Result:
SqlException.
Error severity: 20
Message: Error.
A severe error occurred on the current command.  The results, if any, should be
discarded.
-------------------------------------

Queries 6, 7 and 8

These show how the RAISERROR interacts with a SQL TRY...CATCH block. The RAISERROR within the TRY block only fires the InfoMessage handler in the severity 20 case with the RAISERROR in the CATCH block only firing the handler in the severity 16 case.

Conclusion

It seems like you could build in some quite nice fine grain logging into your SQL statements, stored procs etc by using hooking up a SqlConnection InfoMessage handler and setting FireInfoMessageEventOnUserErrors to true. What's more is that you could write this information out to your application's log file along with the Debug or Trace calls from your code. Considering in some cases you may have quite a lot of logic in a stored procedure especially it may turn out to be really helpful having all your debugging information in one place.