All About RAISERROR

In the online instructions for the script that creates the TSQLTutorJoins sample database from my earlier columns, I recommend that you use osql.exe to run the script from the command prompt. To demonstrate why, I'm basing this month's column on RAISERROR and a cool trick I learned about using the RAISERROR statement's state parameter. Using a special value for the RAISERROR state parameter, you can force the termination of a complex script and prevent its execution in the wrong database.

RAISERROR has three primary components: the error text, the severity, and the state. The error text can be either a hard-coded or parameterized message or an error number from a permanent user-defined message. To create your own permanent messages, see SQL Server Books Online (BOL) about how to use the system stored procedure sp_addmessage.

Severity has several defined levels. Developer-defined errors range in severity from 1 to 16, with 16 being the most common and the default. However, not all severities work the same way. Table 1 shows the severity categories, how they display messages in Query Analyzer, and how they're optionally logged in the Event Viewer's Application log. To log messages to the Event Viewer, you can use WITH LOG in your RAISERROR statement or create the permanent message by using sp_addmessage with the with_log parameter set to 'TRUE'. The latter choice will write every occurrence of this error to the Event Viewer's Application log, even if RAISERROR doesn't specify WITH LOG. The simplified RAISERROR syntax is

RAISERROR (error, severity, state) WITH LOG

For example,

RAISERROR ('Test Severity 16', 16, 1) WITH LOG

returns the following error to the messages window in Query Analyzer:

Error Number InfoServer: Msg 50000, Level 16, State 1, Line 1Error Text    Test Severity 16

The first line is the Error Number Info, as Table 1 describes. Query Analyzer doesn't display this information for severity 10. The display color changes from black for severities 1 through 9 to red for 11 and higher. The sample script in Listing 1 shows additional syntax and ideas for using RAISERROR, including using multiple languages and parameterization. For more information about using RAISERROR and the various severities, see BOL under the following topics: Error Messages, Error Message Severity Levels, RAISERROR, Using RAISERROR, FORMATMESSAGE, and xp_logevent.

In addition to severity, RAISERROR also supports a state. Many object-creation scripts create a database and then tables, procedures, and so on within the newly created database. But what if the script didn't create the database properly? All the additional objects (e.g., tables, procedures) would be created in your default database, which for systems administrators might be master. Creating all your user-defined database objects in the master database is exactly what you don't want, so when you're scripting an automated process, you can include a value for state that terminates the script if a fatal (by your definition) error occurs.

However, setting the state value doesn't always appear to terminate the session. Applications such as Query Analyzer might automatically reconnect when a connection is broken. So, to fully see the benefit of the state option, you need to use a tool such as osql.exe, which doesn't reconnect automatically after a connection is broken. Here's a way to test the state option. From the command prompt, type

osql -E -q"RAISERROR('Test Severity 16', 16, 1) WITH LOG"

This code returns

Test Severity 161>

and you remain in osql.exe. Running the following line from a command prompt:

osql -E -q"RAISERROR('Test State 127', 16, 127) WITH LOG"

returns the error message

Test State 127

and returns you to the command prompt, exiting from osql.exe. If you use osql.exe to raise an error with a state of 127 after a database creation fails, the error state terminates the connection and consequently, the rest of the script, so that you don't create objects in the wrong database.

At the beginning of the database-creation script TSQLTutorJoins.sql, available from the "Download the Code" link at the top of the page, is the following code:

IF DATABASEPROPERTYEX('TSQLTutorJoins', 'COLLATION') IS NULLBEGIN  RAISERROR ('The TSQLTutorJoins database  WAS NOT created. more error info...',  16, 127)END

If the database isn't created, the connection is broken and the object-creation part of the script doesn't execute. I set it up this way to let you determine what the errors are (e.g., out of disk space, incorrect path) before the remainder of the script executes. Without this code, if the database creation fails and the script continues, it would create all the test objects in your default database.

When you're automating scripts, terminating execution on a severe error can be extremely useful. By raising an error with a high severity, logging it to the Event Viewer's Application log, and more important, raising it with a state of 127, you ensure that no script objects are created in the wrong database. Additionally, by logging it in the Event Viewer's Application log, you have an audit trail of the event.