| title | sp_update_alert (Transact-SQL) | ||
|---|---|---|---|
| description | Updates the settings of an existing alert. | ||
| author | markingmyname | ||
| ms.author | maghan | ||
| ms.reviewer | randolphwest | ||
| ms.date | 06/23/2025 | ||
| ms.service | sql | ||
| ms.subservice | system-objects | ||
| ms.topic | reference | ||
| f1_keywords |
|
||
| helpviewer_keywords |
|
||
| dev_langs |
|
[!INCLUDE SQL Server]
Updates the settings of an existing alert.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
sp_update_alert
[ @name = ] N'name'
[ , [ @new_name = ] N'new_name' ]
[ , [ @enabled = ] enabled ]
[ , [ @message_id = ] message_id ]
[ , [ @severity = ] severity ]
[ , [ @delay_between_responses = ] delay_between_responses ]
[ , [ @notification_message = ] N'notification_message' ]
[ , [ @include_event_description_in = ] include_event_description_in ]
[ , [ @database_name = ] N'database_name' ]
[ , [ @event_description_keyword = ] N'event_description_keyword' ]
[ , [ @job_id = ] 'job_id' ]
[ , [ @job_name = ] N'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 = ] N'performance_condition' ]
[ , [ @category_name = ] N'category_name' ]
[ , [ @wmi_namespace = ] N'wmi_namespace' ]
[ , [ @wmi_query = ] N'wmi_query' ]
[ ; ]
The name of the alert that is to be updated. @name is sysname, with no default.
A new name for the alert. The name must be unique. @new_name is sysname, with a default of NULL.
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.
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.
A new severity level (from 1 through 25) for the alert definition. Any [!INCLUDE ssNoVersion] message sent to the Windows application log with the specified severity activates 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.
The new waiting period, in seconds, between responses to the alert. @delay_between_responses is int, with a default of NULL.
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.
Specifies whether the description of the [!INCLUDE ssNoVersion] error from the Windows application log should be included in the notification message. @include_event_description_in is tinyint, and can be one or more of these values.
| Value | Description |
|---|---|
0 |
None |
1 |
|
2 |
Pager |
4 |
net send |
7 |
All |
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. Names that are enclosed in brackets ([]) aren't allowed.
A sequence of characters that must be found in the description of the [!INCLUDE ssNoVersion] error in the error message log. @event_description_keyword is nvarchar(100), with a default of NULL. This parameter is useful for filtering object names (for example, customer_table).
Note
[!INCLUDE tsql] LIKE expression pattern-matching characters can't be used.
@job_id is uniqueidentifier, with a default of NULL.
The job identification number. @job_name is sysname, with a default of NULL. If job_id is specified, job_name must be omitted.
Resets the number of times the alert has occurred. @occurrence_count is int, with a default of NULL, and can only be set to 0.
Resets the date the occurrence count was last reset. @count_reset_date is int, with a default of NULL.
Resets the time the occurrence count was last reset. @count_reset_time is int, with a default of NULL.
Resets the date the alert last occurred. @last_occurrence_date is int, with a default of NULL, and can be set only to 0.
Resets the time the alert last occurred. @last_occurrence_time is int, with a default of NULL, and can be set only to 0.
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.
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.
[!INCLUDE ssinternalonly-md]
A value expressed in the format <itemcomparatorvalue>. @performance_condition is nvarchar(512), and consists of the following 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 |
The name of the alert category. @category_name is sysname, with a default of NULL.
The WMI namespace to query for events. @wmi_namespace is sysname, with a default of NULL.
The query that specifies the WMI event for the alert. @wmi_query is nvarchar(512), with a default of NULL.
0 (success) or 1 (failure).
Only sysmessages written to the Windows 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.
To run this stored procedure, users must be a member of the sysadmin fixed server role.
The following example changes the enabled setting of Test Alert to 0.
USE msdb;
GO
EXECUTE dbo.sp_update_alert
@name = N'Test Alert',
@enabled = 0;
GO