sp_update_alert
Updates the settings of an existing alert.
Syntax
sp_updatealert [@name =] 'name'
[, [@new_name =] 'new_name']
[, [@enabled =] enabled]
[, [@message_id =] message_id]
[, [@severity =] severity]
[, [@delay_between_responses =] delay_between_responses]
[, [@notification_message =] 'notification_message']
[, [@include_event_description_in =] include_event_description_in]
[, [@database_name =] 'database_name']
[, [@event_description_keyword =] 'event_description_keyword']
[, [@job_id =] job_id | [@job_name =] 'job_name']
[, [@occurrence_count = ] occurrence_count]
[, [@count_reset_date =] count_reset_date]
[, [@count_reset_time =] count_reset_time]
[, [@last_occurrence_date =] last_occurrence_date]
[, [@last_occurrence_time =] last_occurrence_time]
[, [@last_response_date =] last_response_date]
[, [@last_response_time =] last_response _time]
[, [@raise_snmp_trap =] raise_snmp_trap]
[, [@performance_condition =] 'performance_condition']
[, [@category_name =] 'category']
Arguments
[@name =] 'name'
Is the name of the alert that is to be updated. name is sysname, with no default.
[@new_name =] 'new_name'
Is a new name for the alert. The name must be unique. new_name is sysname, with a default of NULL.
[@enabled =] enabled
Specifies whether the alert is enabled (1) or not enabled (0). enabled is tinyint, with a default of NULL. An alert must be enabled to fire.
[@message_id =] message_id
Is a new message or error number for the alert definition. Typically, message_id corresponds to an error number in the sysmessages table. message_id is int, with a default of NULL. A message ID can be used only if the severity level setting for the alert is 0.
[@severity =] severity
Is a new severity level (from 1 through 25) for the alert definition. Any Microsoft® SQL Server™ message sent to the Windows NT® application log with the specified severity will activate the alert. severity is int, with a default of NULL. A severity level can be used only if the message ID setting for the alert is 0.
[@delay_between_responses =] delay_between_responses
Is the new waiting period, in seconds, between responses to the alert. delay_between_responses is int, with a default of NULL.
[@notification_message =] 'notification_message'
Is the revised text of an additional message sent to the operator as part of the e-mail, net send, or pager notification. notification_message is nvarchar(512), with a default of NULL.
[@include_event_description_in =] include_event_description_in
Is whether the description of the SQL Server error from the Windows NT application log should be included in the notification message. include_event_description_in is tinyint, with a default of NULL, and can be one or more of these values.
| Value | Description |
|---|---|
| 0 | None |
| 1 | |
| 2 | Pager |
| 4 | net send |
[@database_name =] 'database_name'
Is the name of the database in which the error must occur for the alert to fire. database_name is sysname, with a default of NULL.
[@event_description_keyword =] 'event_description_keyword'
Is a sequence of characters that must be found in the description of the error in the error message log. Transact-SQL LIKE expression pattern-matching characters can be used. event_description_keyword is nvarchar(100), with a default of NULL. This parameter is useful for filtering object names (for example, %customer_table%).
[@job_id =] job_id
Is the job identification number. job_id is uniqueidentifier, with a default of NULL. If job_id is specified, job_name must be omitted.
[@job_name =] 'job_name'
Is the name of the job that executes in response to this alert. job_name is sysname, with a default of NULL. If job_name is specified, job_id must be omitted.
[@occurrence_count = ] occurrence_count
Resets the number of times the alert has occurred. occurrence_count is int, with a default of NULL, and can be set only to 0.
[@count_reset_date =] count_reset_date
Resets the date the occurrence count was last reset. count_reset_date is int, with a default of NULL.
[@count_reset_time =] count_reset_time
Resets the time the occurrence count was last reset. count_reset_time is int, with a default of NULL.
[@last_occurrence_date =] last_occurrence_date
Resets the date the alert last occurred. last_occurrence_date is int, with a default of NULL, and can be set only to 0.
[@last_occurrence_time =] last_occurrence_time
Resets the time the alert last occurred. last_occurrence_time is int, with a default of NULL, and can be set only to 0.
[@last_response_date =] last_response_date
Resets the date the alert was last responded to by the SQLServerAgent service. last_response_date is int, with a default of NULL, and can be set only to 0.
[@last_response_time =] last_response_time
Resets the time the alert was last responded to by the SQLServerAgent service. last_response_time is int, with a default of NULL, and can be set only to 0.
[@raise_snmp_trap =] raise_snmp_trap
Reserved.
[@performance_condition =] 'performance_condition'
Is a value expressed in the format 'item comparator value'. performance_condition is nvarchar(512), with a default of NULL, and consists of these elements.
| Format element | Description |
|---|---|
| Item | A performance object, performance counter, or named instance of the counter |
| Comparator | One of these operators: >, <, = |
| Value | Numeric value of the counter |
[@category_name =] 'category'
The name of the alert category. category is sysname with a default of NULL.
Return Code Values
0 (success) or 1 (failure)
Remarks
sp_update_alert must be run from the msdb database.
Only sysmessages written to the Microsoft® Windows NT® application log can fire an alert.
sp_update_alert changes only those alert settings for which parameter values are supplied. If a parameter is omitted, the current setting is retained.
Permissions
Only members of the sysadmin fixed server role can execute sp_update_alert.
Examples
This example changes the enabled setting of Test Alert to 0.
sp_updatealert @name = 'Test Alert', @enabled = 0