SQL Server Agent Jobs that Have No Notification and Notification Change
If you want to verify each of your SQL Agent jobs have
email notification then you can run this code:
USE
msdb
SELECT
name, notify_email_operator_id
FROM dbo.sysjobs
USE msdb
SELECT name, notify_email_operator_id
FROM dbo.sysjobs
WHERE notify_email_operator_id = 0;
OR
SELECT @@servername as
Server_name, j.name
AS JobName,
e.name AS EmailOperator
,j.enabled As Enabled
FROM
msdb.dbo.sysjobs
j
LEFT JOIN msdb.dbo.sysoperators e
on j.notify_email_operator_id =
e.id
where j.enabled=1
1=enabled
0=Disabled
Based on requirement please change the where condition.
IF server doesn't have any operator use below script to create a new operator. Below script I created an operator named "DBA-SQL" and
associated it with the email address “DBA-SQL@Company.com” or else use GUI from SSMS.
USE
[msdb]
GO
EXEC
msdb.dbo.sp_add_operator @name=N'DBA-SQL',
@enabled=1,
@email_address=N'DBA-SQL@compenay.com'
GO
Automatically Updating SQL Agent Jobs
to Have Notification:
If you already have a number of SQL Server agent jobs in one server where notification hasn’t been set up you might want to set up each job to
notify someone.
If you have a lot of SQL Agent jobs it might take a
while for you to go through each job to define an email notification operator
manually through the SQL Agent GUI.
A better way to change a bunch of SQL
Agent jobs to send email notifications would be to run a script.
Here is
a sample script for defining an operator on each job that doesn’t already have
an email operator:
USE
[msdb]
SET NOCOUNT ON;
DECLARE
@Operator varchar(50) = 'DBA-SQL' -- place your
operator name here
SELECT 'EXEC sp_update_job @job_name = ''' + j.[name] +
''',
@notify_email_operator_name = ''' +
@Operator +
''',
@notify_level_email = 2'-- 1=On Success, 2=On
Faulure,3=always
FROM dbo.sysjobs j
WHERE j.enabled = 1
AND j.notify_level_email <>
1
This script doesn’t actually add the operators to SQL
Agent jobs that don’t have email notification. But instead it just
creates a series of EXEC statements that when executed add the email
notifications. The series of EXEC statements that this script generates
will run the sp_update_job system stored procedures to add an email
operator. This particular script only adds email notification on a job
that doesn’t already have email notification. Additionally, it only sets
up notification to occur when the job execution fails. If you
should happen to want different operators to be notified for different jobs
then you will either need to add some special code to place the appropriate
operator on each job, or run the above script then modify the operators for
each job prior to executing the generated script.
set nocount on
DECLARE
@strsql NVARCHAR(MAX)
DECLARE
@job_list cursor
set
@job_list = cursor
for
SELECT 'EXEC
msdb..sp_update_job @job_name = ''' + j.[name] + ''', @notify_email_operator_name = ''DBA-SQL'',
@notify_level_email = 2' [exec_sql]
FROM
msdb.dbo.sysjobs
j
LEFT JOIN msdb.dbo.sysoperators e
on j.notify_email_operator_id =
e.id
where j.enabled = 1 --Change according to you requirement
open
@job_list
fetch next from @job_list into @strsql
while @@FETCH_STATUS = 0
begin
exec sp_executesql @strsql
fetch next from @job_list into @strsql
end
close
@job_list
deallocate
@job_list
