Error Reporting

for RuBoard

We'll start off with a quick overview of the tools at your disposal for reporting errors to Transact-SQL code and to client applications. Transact-SQL's error reporting tools are neither elegant nor full featured, but they can usually be made to do what you want.

RAISERROR

You report errors from stored procedures via return codes and the RAISERROR command. RAISERROR doesn't exit from the procedure, it simply returns the specified error message and sets the @@ERROR automatic variable. You can pass your own error message to RAISERROR to return, or you can reference a predefined error number/message combo in the sysmessages table. An error number is always returned by RAISERROR, regardless of whether you actually specify one. If you supply a custom message to RAISERROR, the error number is set to 50000, the maximum system-defined error number ( user error numbers start at 50001).

You can format RAISERROR messages similarly to printf(). The message itself is the format string (complete with format specifiers such as %d and %s), and you can specify as many arguments as necessary to plug values into it. You can also add your own messages to the sysmessages table via sp_addmessage. These messages can contain format specifiers as well.

You specify both a severity and a state when using RAISERROR to return an error message. Errors with a severity less than 16 produce informational messages in the system event log (when logged), errors with a severity of 16 produce warning messages in the event log, and errors with severity values greater than 16 produce error messages in the event log. Any user can specify severity values up through 18; severity values 19 through 25 can only be raised by members of the sysadmin role and require the use of the WITH LOG option. Errors with severity values more than 20 are considered fatal and cause the client connection to terminate.

State is an informational value that you can return from RAISERROR to indicate state information to your front-end application. Specify a state value of 127 to cause the ISQL and OSQL utilities to set the operating system ERRORLEVEL environmental variable to the error number returned by RAISERROR. In releases of SQL Server prior to 7.0, ISQL exited immediately when a state of 127 was used. This is no longer true. Now it merely sets ERRORLEVEL. OSQL, however, still exits immediately.

RAISERROR supports several parameters that control how it works. The WITH LOG option copies the error message you raise to the application event log ( assuming there is one) and the SQL Server error log regardless of whether the message was added to sysmessages using sp_addmessage's with_log option. WITH NOWAIT causes the message to be returned immediately to the client. Raising an error with NOWAIT also flushes the connection's output buffer, so it's a nice way to force PRINT and other pending messages to be sent immediately to the client. For example, to flush the output buffer without actually raising an error, you could do this:

 RAISERROR ('', 0, 1) WITH NOWAIT 

Any pending messages will be sent immediately to the client.

The WITH SETERROR option forces the automatic @@ERROR variable to return the last error number, regardless of its severity. This is handy for reporting user-defined informational messages using @@ERROR.

Use the system procedure sp_addmessage to add messages to the sysmessages table. Once added, these messages can be raised by RAISERROR. User messages must have error numbers of 50001 or higher. A big advantage of using SQL Server's messages facility is that it's language independent. Because you can specify a language with each message you add, you can have several messages with the same error number, but different language settings. You then raise errors using their codes, and the message that's returned depends on the language setting the user chose when installing SQL Server.

xp_logevent

Use the xp_logevent extended procedure to add a message to the SQL Server error log or the NT event log without alerting the user. Messages logged by xp_logevent are not sent to the client. The message number or string you pass to xp_logevent is silently logged without client notification.

for RuBoard