sp_help_alert

Transact-SQL Reference

Transact-SQL Reference

sp_help_alert

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™ version 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 (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 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 e-mail 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'

See Also

sp_add_alert

sp_update_alert

System Stored Procedures