sp_help_alert (Transact-SQL)
Reports information about the alerts defined for the server.
Syntax
sp_help_alert [ [ @alert_name = ] 'alert_name' ]
[ , [ @order_by = ] 'order_by' ]
[ , [ @alert_id = ] alert_id ]
[ , [ @category_name = ] 'category' ]
[ , [ @legacy_format = ] legacy_format ]
Arguments
- [ @alert_name =] 'alert_name'
The alert name. alert_name is nvarchar(128). If alert_name is not specified, information about all alerts is returned.
- [ @order_by =] 'order_by'
The sorting order to use for producing the results. order_byis sysname, with a default of N 'name'.
- [ @alert_id =] alert_id
The identification number of the alert to report information about. alert_idis int, with a default of NULL.
- [ @category_name =] 'category'
The category for the alert. category is sysname, with a default of NULL.
- [ @legacy_format=] legacy_format
Is whether to produce a legacy result set. legacy_format is bit, with a default of 0. When legacy_format is 1, sp_help_alert returns the result set returned by sp_help_alert in Microsoft SQL Server 2000.
Return Code Values
0 (success) or 1 (failure)
Result Sets
When @legacy_format is 0, sp_help_alert produces the following result set.
| Column name | Data type | Description |
|---|---|---|
id |
int |
System-assigned unique integer identifier. |
name |
sysname |
Alert name (for example, Demo: Full msdb log). |
event_source |
nvarchar(100) |
Source of the event. It will always be MSSQLServer for Microsoft SQL Server version 7.0 |
event_category_id |
int |
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. |
event_id |
int |
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. |
message_id |
int |
Message error number that defines the alert. (Usually corresponds to an error number in the sysmessages table). If severity is used to define the alert, message_id is 0 or NULL. |
severity |
int |
Severity level (from 9 through 25, 110, 120, 130, or 140) that defines the alert. |
enabled |
tinyint |
Status of whether the alert is currently enabled (1) or not (0). A nonenabled alert is not sent. |
delay_between_responses |
int |
Wait period, in seconds, between responses to the alert. |
last_occurrence_date |
int |
Data the alert last occurred. |
last_occurrence_time |
int |
Time the alert last occurred. |
last_response_date |
int |
Date the alert was last responded to by the SQLServerAgent service. |
last_response_time |
int |
Time the alert was last responded to by the SQLServerAgent service. |
notification_message |
nvarchar(512) |
Optional additional message sent to the operator as part of the e-mail or pager notification. |
include_event_description |
tinyint |
Is whether the description of the SQL Server error from the Microsoft Windows application log should be included as part of the notification message. |
database_name |
sysname |
Database in which the error must occur for the alert to fire. If the database name is NULL, the alert fires regardless of where the error occurred. |
event_description_keyword |
nvarchar(100) |
Description of the SQL Server error in the Windows application log that must be like the supplied sequence of characters. |
occurrence_count |
int |
Number of times the alert occurred. |
count_reset_date |
int |
Date the occurrence_count was last reset. |
count_reset_time |
int |
Time the occurrence_count was last reset. |
job_id |
uniqueidentifier |
Identification number of the job to be executed in response to an alert. |
job_name |
sysname |
Name of the job to be executed in response to an alert. |
has_notification |
int |
Nonzero if one or more operators are notified for this alert. The value is one or more of the following values (ORed together): 1=has e-mail notification 2=has pager notification 4=has net send notification. |
flags |
int |
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. |
performance_condition |
nvarchar(512) |
If type is 2, this column shows the definition of the performance condition; otherwise, the column is NULL. |
category_name |
sysname |
Reserved. Will always be '[Uncategorized]' for SQL Server 7.0. |
wmi_namespace |
sysname |
If type is 3, this column shows the namespace for the WMI event. |
wmi_query |
nvarchar(512) |
If type is 3, this column shows the query for the WMI event. |
type |
int |
Type of the event: 1 = SQL Server event alert 2 = SQL Server performance alert 3 = WMI event alert |
When @legacy_format is 1, sp_help_alert produces the following result set.
| Column name | Data type | Description |
|---|---|---|
id |
int |
System-assigned unique integer identifier. |
name |
sysname |
Alert name (for example, Demo: Full msdb log). |
event_source |
nvarchar(100) |
Source of the event. It will always be MSSQLServer for SQL Server version 7.0 |
event_category_id |
int |
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. |
event_id |
int |
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. |
message_id |
int |
Message error number that defines the alert. (Usually corresponds to an error number in the sysmessages table). If severity is used to define the alert, message_id is 0 or NULL. |
severity |
int |
Severity level (from 9 through 25, 110, 120, 130, or 140) that defines the alert. |
enabled |
tinyint |
Status of whether the alert is currently enabled (1) or not (0). A nonenabled alert is not sent. |
delay_between_responses |
int |
Wait period, in seconds, between responses to the alert. |
last_occurrence_date |
int |
Data the alert last occurred. |
last_occurrence_time |
int |
Time the alert last occurred. |
last_response_date |
int |
Date the alert was last responded to by the SQLServerAgent service. |
last_response_time |
int |
Time the alert was last responded to by the SQLServerAgent service. |
notification_message |
nvarchar(512) |
Optional additional message sent to the operator as part of the e-mail or pager notification. |
include_event_description |
tinyint |
Is whether the description of the SQL Server error from the Windows application log should be included as part of the notification message. |
database_name |
sysname |
Database in which the error must occur for the alert to fire. If the database name is NULL, the alert fires regardless of where the error occurred. |
event_description_keyword |
nvarchar(100) |
Description of the SQL Server error in the Windows application log that must be like the supplied sequence of characters. |
occurrence_count |
int |
Number of times the alert occurred. |
count_reset_date |
int |
Date the occurrence_count was last reset. |
count_reset_time |
int |
Time the occurrence_count was last reset. |
job_id |
uniqueidentifier |
Job identification number. |
job_name |
sysname |
An on-demand job to be executed in response to an alert. |
has_notification |
int |
Nonzero if one or more operators are notified for this alert. The value is one or more of the following values (joined together with OR): 1=has e-mail notification 2=has pager notification 4=has net send notification. |
flags |
int |
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. |
performance_condition |
nvarchar(512) |
If type is 2, this column shows the definition of the performance condition. If type is 3, this column shows the query for the WMI event. Otherwise, the column is NULL. |
category_name |
sysname |
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. Will always be '[Uncategorized]' for Microsoft SQL Server 7.0. |
type |
int |
Type of alert: 1 = SQL Server event alert 2 = SQL Server performance alert 3 = WMI event alert |
Remarks
sp_help_alert must be run from the msdb database.
Permissions
By default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted the SQLAgentOperatorRole fixed database role in the msdb database.
For details about SQLAgentOperatorRole, see SQL Server Agent Fixed Database Roles.
Examples
The following example reports information about the Demo: Sev. 25 Errors alert.
USE msdb ;
GO
EXEC sp_help_alert @alert_name = 'Demo: Sev. 25 Errors'
GO
See Also
Reference
sp_add_alert (Transact-SQL)
sp_update_alert (Transact-SQL)
System Stored Procedures (Transact-SQL)