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 :

• One or more notifications sent via e-mail and/or pager

  and/or

• A task to execute

  By setting this value it is possible to prevent, for example, an unwanted flood of e-mail messages from being sent when an alert repeatedly occurs in a short period of time. If a value is not supplied, the default is 0, which means there is no waiting between responses (each occurrence of the alert generates a response).

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:

• Severity 19 or higher sysmessages errors
• information/warnings/errors from the server (severities 110, 120, and 130)
• Any RAISERROR statement invoked with WITH_LOG syntax
• Any user-defined messages in the sysmessages table that were created with a WITH_LOG option of 'TRUE'
• Any sysmessages error modified using the sp_altermessage SQL executive stored procedure (for example, sp_altermessage 123456, with_log, true)
• Any event logged using the xp_logevent extended stored procedure

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:

• The SQL Executive service is running
• The event appeared in the Windows NT event log
• The alert is enabled

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