sp_help_alert (Transact-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' ] 
     [ , [ @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

Reserved. 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 Microsoft 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

See Also

Reference

sp_add_alert (Transact-SQL)
sp_update_alert (Transact-SQL)
System Stored Procedures (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance