Create an Alert Using an Error Number

Applies to: SQL Server

This topic describes how to create a Microsoft SQL Server Agent alert occurs in SQL Server that will be raised when an error of a specific number occurs by using SQL Server Management Studio or Transact-SQL.

Before You Begin

Limitations and Restrictions

• SQL Server Management Studio provides an easy, graphical way to manage the entire alerting system and is the recommended way to configure an alert infrastructure.

• Events generated with xp_logevent occur in the master database. Therefore, xp_logevent does not trigger an alert unless the @database_name for the alert is 'master' or NULL.

Security

Permissions

By default, only members of the sysadmin fixed server role can execute sp_add_alert.

Using SQL Server Management Studio

To create an alert using an error number

1. In Object Explorer, click the plus sign to expand the server where you want to create an alert using an error number.

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

3. Right-click Alerts and select New Alert.

4. In the New Alert dialog box, in the Name box, enter a name for this alert.

5. Check the Enable check box to enable the alert to run. By default, Enable is checked.

6. In the Type list, select SQL Server event alert.

7. Under Event alert definition, in the Database name list, select a database to restrict the alert to a specific database.

8. Under Alerts will be raised based on, click Error number, and then type a valid error number for the alert. Alternately, click Severity and then select the specific severity that will raise the alert.

9. Check the box corresponding to Raise alert when message contains check box to restrict the alert to a particular character sequence, and then enter a keyword or character string for the Message text. The maximum number of characters is 100.

10. Click OK.

Using Transact-SQL

To create an alert using an error number

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 alert (Test Alert) that runs the Back up
   -- the AdventureWorks2022 Database job when fired   
   -- assumes that the message 55001 and the Back up
   -- the AdventureWorks2022 Database job already exist.  
   USE msdb ;  
   GO  
   
   EXEC dbo.sp_add_alert  
       @name = N'Test Alert',  
       @message_id = 55001,   
      @severity = 0,   
      @notification_message = N'Error 55001 has occurred. The DB will be backed up...',   
      @job_name = N'Back up the AdventureWorks2022 Database' ;  
   GO  
   

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