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 |
| 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'