Create an email operator to send notification recipient for alerts, jobs on SQL Server Agent.

Problem: How to send notifications of alerts and jobs to a recipient.?

Solution: sp_add_operator

Under SQL Server Agent Stored Procedures, there is a system stored procedure which allows to create an operator and configure the email address and name to notify about alerts or jobs.

It also has an option to configure a way to send notification on a timely basis like weekdays from 9 am to 5 pm.

This kind of helps when you want to send notifications to different environments like PROD, DEV, TEST and teams associated to those and if an employee changes you can swap the email address in the operator instead of changing it all over in the job notification emails and mail profiles.

The operator’s information is stored on the msdb database where all the jobs and alerts are stored as well.  So when executing the procedure, it should be executed on the context of msdb database.

Avoid using pager to send notifications because in the future versions SQL Server is planning to deprecate the feature.

Example:

In this article I am going to show you we can create a notification using Sp_add_operator and view the results after creating the operator.

Use [msdb]

GO

EXEC msdb.dbo.sp_add_operator @name=N'LearnersSQL',

                                @enabled=1,

                                @email_address=N'learnerssql@gmail.com',

Use [msdb]

GO

-- to view the operators on the server.  

select id,name,enabled,email_address from dbo.sysoperators

Properties	Values
id	1
name	LearnersSQL
enabled	1
email_address	learnerssql@gmail.com