sp_help_alert (T-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']

Arguments

[@alert_name =] 'alert_name': Is the alert name. alert_name is nvarchar(128). If alert_name is not specified, information about all alerts is returned .
[@order_by =] 'order_by': Is the sorting order to use for producing the results. order_by is sysname, with a default of N ‘name’.
[@alert_id =] alert_id]: Is the identification number of the alert to report information about. alert_id is int, with a default of NULL.
[@category_name =] 'category': Is the category for the alert. category is sysname, with a default of NULL.

Return Code Values

0 (success) or 1 (failure)

Result Sets

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™ 7.0
event_category_id	int	Reserved.
event_id	int	Reserved.
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 (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 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 NT® 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 NT 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 (ORed together). 1=has email notification 2=has pager notification 4=has netsend noticication.
flags	int	Reserved.
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.
type	int	1 = SQL Server event alert 2 = SQL Server performance alert

Remarks

sp_help_alert must be run from the msdb database.

Permissions

Only members of the sysadmin fixed server role can execute sp_help_alert .

Examples

This example reports information about the Demo: Sev. 25 Errors alert.

EXEC sp_help_alert 'Demo: Sev. 25 Errors'