Artikel
10/04/2012

sp_help_alert (Transact-SQL)

Reports information about the alerts defined for the server.

Topic link icon Transact-SQL Syntax Conventions

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

Dela via