Problem:
How to send notifications of alerts and jobs to a recipient.?
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
|
0 comments:
Post a Comment