Edit

sp_help_alert (Transact-SQL)

  • Article

Applies to: SQL Server

Reports information about the alerts defined for the server.

Transact-SQL syntax conventions

Syntax

sp_help_alert [ [ @alert_name = ] N'alert_name' ]
    [ , [ @order_by = ] N'order_by' ]
    [ , [ @alert_id = ] alert_id ]
    [ , [ @category_name = ] N'category_name' ]
    [ , [ @legacy_format = ] legacy_format ]
[ ; ]

Arguments

[ @alert_name = ] N'alert_name'

The alert name. @alert_name is nvarchar(128). If @alert_name isn't specified, information about all alerts is returned.

[ @order_by = ] N'order_by'

The sorting order to use for producing the results. @order_by is sysname, with a default of the @alert_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 = ] N'category'

The category for the alert. @category is sysname, with a default of NULL.

[ @legacy_format = ] legacy_format

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 SQL Server 2000 (8.x).

Return code values

0 (success) or 1 (failure).

Result set

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 isn't 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 SQL Server Agent service.
last_response_time int Time the alert was last responded to by the SQL Server Agent 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 Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. 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 isn't 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 SQL Server Agent service.
last_response_time int Time the alert was last responded to by the SQL Server Agent 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 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

Related content