Defining Alerts to Handle Errors

An alert can be defined to raise a response to a particular error number or to the group of errors that belongs to a specific severity code. Furthermore, the definition of an alert for a particular error is different for system errors and user-defined errors. (The creation of alerts on user-defined errors is described below in this page.)

The rest of this section shows how you can create alerts using SQL Server Management Studio.

Creating Alerts on System Errors

Example 13.5, in section Affecting Locks, Displaying Lock Information, Deadlock, in which one transaction was deadlocked by another transaction, will be used to show how to create an alert about a system error number. If a transaction is deadlocked by another transaction, the victim must be executed again. This can be done, among other ways, by using an alert.

To create the deadlock (or any other) alert, expand SQL Server Agent, right-click Alerts, and choose New Alert. In the New Alert dialog box (see Figure 17-7), enter the name of the alert in the Name box, choose SQL Server Event Alert in the Type drop-down list, and choose from the Database Name drop-down list. Click the Error Number radio button, and enter 1205. (This error number indicates a deadlock problem, where the current process was selected as the “victim.”)

The second step defines the response for the alert. In the same dialog box, click the Response page (see Figure 17-8). First check Execute Job, and then choose the job to execute when the alert occurs. (The example here defines a new job called deadlock_ all_db that restarts the victim transaction.) Check Notify Operators, and then, in the Operator List pane, select operators and choose the methods of their notifications (e-mail, pager, and/or the net send command).

Note – In the preceding example, it is assumed that the victim process will be terminated. Actually, after receiving the deadlock error 1205, the program resubmits the failed transaction on its own.

Creating Alerts on Error Severity Levels

You can also define an alert that will raise a response on error severity levels. As you already know, each system error has a corresponding severity level that is a number between 0 and 25. The higher the severity level is, the more serious the error. Errors with severity levels 20 through 25 are fatal errors. Errors with severity levels 19 through 25 are written to the Windows Application log.

Note – Always define an operator to be notified when a fatal error occurs.

As an example of how you can create alerts in relation to severity levels, here’s how you use SQL Server Management Studio to create the particular alert for severity level 25. First, expand SQL Server Agent, right-click Alerts, and choose New Alert. In the Name box, enter a name for this alert (for example, Severity 25 errors). In the Type drop-down list, choose SQL Server event alert. In the Database Name drop-down list, choose the sample database. Click the Severity radio button and choose 025 – Fatal Error.

On the Response page, enter one or more operators to be notified via e-mail, pager, and/or the net send command when an error of severity level 25 occurs.

Creating Alerts on User-Defined Errors

In addition to creating alerts on system errors, you can create alerts on customized error messages for individual database applications. Using such messages (and alerts), you can define solutions to problems that might occur in an application.

The following steps are necessary if you want to create an alert on a user-defined message:

1. Create the error message.
2. Raise the error from a database application.
3. Define an alert on the error message.

An example is the best way to illustrate the creation of such an alert: the alert is fired if the shipping date of a product is earlier than the order date. (For the definition of the sales table, see Chapter 5.)

Note – Only the first two steps are described here, because an alert on a user-defined message is defined similarly to an alert on a system error message.

Creating an Error Message

To create a user-defined error message, you can use either SQL Server Management Studio or the sp_addmessage stored procedure. Example 17.2 creates the error message for the example using the sp_addmessage stored procedure.

Example 17.2

 sp_addmessage @msgnum=50010, @severity=16,
@msgtext='The shipping date of a product is earlier than the order date',
@lang='us_english', @with_log='true'Code language: SQL (Structured Query Language) (sql)

The sp_addmessage stored procedure in Example 17.2 creates a user-defined error message with error number 50010 (the @msgnum parameter) and severity level 16 (the @severity parameter). All user-defined error messages are stored in the sysmessages system table of the master database and can be viewed by using the sys.messages catalog view. The error number Example 17.2 is 50010 because all user-defined errors must be greater than 50000. (All error message numbers less than 50000 are reserved for the system.)

For each user-defined error message, you can optionally use the @lang parameter to specify the language in which the message is displayed. This specification may be necessary if multiple languages are installed on your computer. (When the @lang parameter is omitted, the session language is the default language.)

By default, user-defined messages are not written to the Windows Application log. On the other hand, you must write the message to this log if you want to raise an alert on it. If you set the @with_log parameter of the sp_addmessage system procedure to TRUE, the message will be written to the log.

Raising an Error Using Triggers

To raise an error from a database application, you invoke the RAISERROR statement. This statement returns a user-defined error message and sets a system flag in the @@error global variable. (You can also handle error messages using TRY/CATCH blocks.)

Example 17.3 creates the trigger t_date_comp, which returns a user-defined error of 50010 if the shipping date of a product is earlier than the order date.

Note – To execute Example 17.3, the table sales must exist (see Example 5.21 in section “Altering a Table – ALTER TABLE statement“).

Example 17.3

USE sample;
GO
CREATE TRIGGER t_date_comp
	ON sales
	FOR INSERT AS
	DECLARE @order_date DATE
	DECLARE @shipped_date DATE
SELECT @order_date=order_date, @shipped_date=ship_date FROM INSERTED
	IF @order_date > @shipped_date
		RAISERROR (50010, 16, -1)Code language: SQL (Structured Query Language) (sql)

Now, if you insert the following row in the sales table, the shipping date of a product is earlier than the order date:

insert into sales values (1, '01.01.2007', '01.01.2006')Code language: SQL (Structured Query Language) (sql)

the system will return the user-defined error message:

Msg 50010, Level 16, State 1, Procedure t_date_comp, Line 8Code language: SQL (Structured Query Language) (sql)