sp_addalert SQL Executive Stored Procedure

Creates an alert.

Syntax

sp_addalert name, message_id, severity [, enabled] [, delay_between_responses] [, notification_message] [, include_event_description_in] [, database_name] [, event_description_keyword] [, task_name]

where

name
Is the name of the alert (for example, 605 Alert). This name will appear in the e-mail and/or page sent in response to the alert. It must be unique and be no more than 60 characters.
message_id
Is the message error number that defines the alert. (Normally corresponds to an error number in the sysmessages table.) If severity is used to define the alert, message_id must be zero or NULL. Note that only sysmessages errors written to the event log can cause an alert to be sent.
severity
Specifies the severity level (9 through 25, 110, 120, 130, or 140) that defines the alert. Any SQL Server message stored in the sysmessages table that is sent to the Windows NT event log with the indicated severity will cause the alert to be sent. If message_id is used to define the alert, severity must be zero or NULL.
enabled
Indicates whether the alert is currently enabled (1 if enabled, 0 if not). A non-enabled alert will not be sent. The default is enabled (1).
delay_between_responses
Specifies the wait period, in seconds, between responses to the alert. The response consists of :
notification_message
Is an optional additional message that will be sent to the operator as part of the e-mail and/or pager notification. Specifying notification_message is useful for adding special notes such as remedial procedures. It can contain as many as 255 characters.
include_event_description_in
Specifies whether the description of the SQL Server error from the Windows NT event log should be included as part of the notification message. Must be NONE, EMAIL, PAGER or BOTH.
database_name
More restrictively defines the alert by specifying the database in which the error must occur in order for the alert to fire. If database_name is not supplied, the alert will fire regardless of in which database the error occurred.
event_description_keyword
More restrictively defines the alert by specifying that the description of the SQL Server error in the Windows NT event log must be like the supplied sequence of characters. Transact-SQL LIKE expression pattern-matching characters can be used. This parameter is useful for filtering object names (for example, %customer_table%). It can contain as many as 100 characters.
task_name
Specifies an On-Demand task to be executed in response to an alert. The task must be of type CmdExec or TSQL. For details, see the sp_addtask SQL Executive Stored Procedure.

Remarks

This stored procedure must be run from the msdb database.

The following summarizes the circumstances under which errors/messages generated by SQL Server and SQL Server applications are sent to the Windows NT event log and can therefore raise alerts:

SQL Enterprise Manager provides an easy, graphical way to manage the entire alerting system. Using SQL Enterprise Manager is the recommended way to configure your alert infrastructure.

If an alert is not functioning properly, check the following:

Example

This example adds an alert ('Test Alert') that invokes the task 'Back up the Customer Database,' when fired.

EXEC sp_addalert @name = 'Test Alert', @message_id = 55001, @severity = NULL, @notification_message = 'Error 55001 has occurred. The database will be backed up...', @task_name = 'Back up the Customer Database'

Permission

Only the system administrator can use this procedure.

Tables Used

sysalerts, systasks

See Also

sp_addnotification sp_helpalert
sp_addtask sp_updatealert
sp_dropalert