Define the Response to an Alert (SQL Server Management Studio)

This topic describes how to define how Microsoft SQL Server responds to SQL Server Agent alerts in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL.

In This Topic

• Before you begin:

  Limitations and Restrictions

  Security

• To define the response to an alert, using:

  SQL Server Management Studio

  Transact-SQL

Before You Begin

Limitations and Restrictions

• The Pager and net send options will be removed from SQL Server Agent in a future version of Microsoft SQL Server. Avoid using these features in new development work, and plan to modify applications that currently use these features.

• Note that SQL Server Agent must be configured to use Database Mail to send e-mail and pager notifications to operators. For more information, see Assign Alerts to an Operator.

• SQL Server Management Studio provides an easy, graphical way to manage jobs, and is the recommended way to create and manage the job infrastructure.

Security

Permissions

Only members of the sysadmin fixed server role can define the response to an alert.

[Top]

Using SQL Server Management Studio

To define the response to an alert

1. In Object Explorer, click the plus sign to expand the server that contains the alert on which you want to define a response.

2. Click the plus sign to expand SQL Server Agent.

3. Click the plus sign to expand the Alerts folder.

4. Right-click the alert on which you want to define a response and select Properties.

5. In the alert_name alert properties dialog box, under Select a page, select Response.

6. Select the Execute job check box and, from the list below the Execute job check box, select a job to execute when the alert occurs. You can create a new job by clicking New Job. You can view more information about the job by clicking View Job. For more information about the available options in the New Job and Job Properties job_name dialog boxes, see Create a Job and View a Job.

7. Select the Notify Operators check box if you want to notify operators when the alert is activated. In the Operator list, select one or more of the following methods for notifying the operator or operators: E-mail, Pager, or Net Send. You can create a new operator by clicking New Operator. You can view more information about an operator by clicking View Operator. For more information about the available options in the New Operator and View Operator Properties dialog boxes, see Create an Operator and View Information About an Operator.

8. When finished, click OK.

[Top]

Using Transact-SQL

To define the response to an alert

1. In Object Explorer, connect to an instance of Database Engine.

2. On the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute.

   -- adds an e-mail notification for Test Alert.
   -- assumes that Test Alert already exists and that François Ajenstat is a valid operator name 
   USE msdb ;
   GO
   
   EXEC dbo.sp_add_notification
    @alert_name = N'Test Alert',
    @operator_name = N'François Ajenstat',
    @notification_method = 1 ;
   GO
   

For more information, see sp_add_notification (Transact-SQL).

[Top]