Skip to content

create-an-alert-using-an-error-number.md

Latest commit

 

History

History
93 lines (64 loc) · 4.41 KB

create-an-alert-using-an-error-number.md

File metadata and controls

93 lines (64 loc) · 4.41 KB
title Create an Alert Using an Error Number
ms.custom seo-lt-2019
ms.date 01/19/2017
ms.prod sql
ms.prod_service sql-tools
ms.technology ssms
ms.topic conceptual
helpviewer_keywords
alerts [SQL Server], creating
SQL Server Agent, alerts
alerts [SQL Server], error numbers
ms.assetid 03dd7fac-5073-4f86-babd-37e45a86023c
author markingmyname
ms.author maghan
ms.manager jroth
ms.reviewer
monikerRange = azuresqldb-mi-current || >= sql-server-2016 || = sqlallproducts-allversions

Create an Alert Using an Error Number

[!INCLUDEappliesto-ss-asdbmi-xxxx-xxx-md]

Important

On Azure SQL Database Managed Instance, most, but not all SQL Server Agent features are currently supported. See Azure SQL Database Managed Instance T-SQL differences from SQL Server for details.

This topic describes how to create a [!INCLUDEmsCoName] [!INCLUDEssNoVersion] Agent alert occurs in [!INCLUDEssCurrent] that will be raised when an error of a specific number occurs by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql].

Before You Begin

Limitations and Restrictions

  • [!INCLUDEssManStudioFull] 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 [!INCLUDEssDE].

  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 AdventureWorks2012 Database job when fired   
-- assumes that the message 55001 and the Back up
-- the AdventureWorks2012 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 AdventureWorks2012 Database' ;  
GO

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