sp_updatealert SQL Executive Stored Procedure

Updates information for an existing alert.

Syntax

sp_updatealert name [, new_name] [, enabled] [, message_id] [, severity] [, delay_between_responses] [, notification_message] [, include_event_description_in] [, database_name] [, event_description_keyword] [, task_name] [, occurrence_count] [, count_reset_date] [, count_reset_time] [, last_occurrence_date] [, last_occurrence_time] [, last_response_date] [, last_response _time]

where

name

Is the original name of the alert.

new_name

Is the new name for the alert. It must be unique and be no more than 60 characters.

enabled

Indicates whether the alert is currently enabled (1 if enabled, 0 if not). An alert must be enabled to run. The default is 1 (enabled).

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, must be zero or NULL.

Note Only sysmessages written to the Windows NT application event log can activate an alert.

severity

Specifies the severity level (9 through 25, 120, 130, or 140) that defines the alert. Any SQL Server message sent to the Windows NT event log with the indicated severity will activate the alert. If message_id is used to define the alert, must be zero or NULL.

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 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 the database in which the error occurs.

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 may 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 the On-Demand task that will execute in response to an alert. The task must be of type 'CmdExec' or 'TSQL'. For details, see the sp_addtask SQL executive stored procedure.

occurrence_count

Indicates the number of times the alert has occurred. This parameter is used for reset purposes only.

count_reset_date

Indicates the date the occurrence count was last reset. This parameter is used for reset purposes only.

count_reset_time

Indicates the time the occurrence count was last reset. This parameter is used for reset purposes only.

last_occurrence_date

Indicates the date the alert last occurred. This parameter is used for reset purposes only.

last_occurrence_time

Indicates the time the alert last occurred. This parameter is used for reset purposes only.

last_response_date

Indicates the date the alert was last responded to by the SQL Executive service. This parameter is used for reset purposes only.

last_response_time

The time the alert was last responded to by the SQL Executive service. This parameter is used for reset purposes only.

Remarks

This stored procedure must be run from the msdb database.

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.

Example

This example updates the alert 'Test Alert' to an enabled status of 0.

sp_updatealert @name = 'Test Alert', @enabled = 0

Permission

Only the system administrator can use this procedure.

Tables Used

sysalerts, systasks