Add e-mail notification to job via TSQL?

Question

Post reply

Add e-mail notification to job via TSQL?

Bryan Welch-438459

Old Hand

Points: 372
More actions
November 15, 2011 at 12:40 pm

#262788

Assuming e-mail is configured for a SQL Server, how may I use SQL to add e-mail notification to an existing job?
I can do this using SQL Server Management Studio by opening the properties for a job under SQL Server Agent, selecting 'Notifications', enabling it, and selecting the e-mail (operator?) from the drop-down list. That will send one e-mail if the job fails. I'm trying to do that same procedure in SQL so I may run one big SQL script to configure notifications for several jobs.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Pam Brisjar SSChampion Points: 12094 More actions · Answer 1

This is what I get from scripting that:

USE [msdb]

GO

EXEC msdb.dbo.sp_update_job @job_id=N'01cc77f0-9ea0-4e26-9cc5-2cdda24086b5',

@notify_level_email=2,

@notify_level_netsend=2,

@notify_level_page=2,

@notify_email_operator_name=N'DBA'

GO

You'd have to take this and join it with the msdb table to get the job ID's and check to see that there isn't already a notify entry.

Of course this implies that you already have a SQL Agent operator set up and that DB Mail is enabled for SQL Agent (which is separate from enabling it for SQL Server).

Interesting idea, though. I think I'll be taking a look at this myself.

dave hants Default port Points: 1485 More actions · Answer 2

I usually set up any new sql server instances so that it runs a job at 7am every day which runs some sql to check all jobs and steps that ran in the past 24 hours.

It then sends me an email saying "passed" if there were no failures. And if there were failures it sends me an email containing the job and step names that failed.

Is that what you are looking for.... ?

Regards,

Dave

Bryan Welch-438459 Old Hand Points: 372 More actions · Answer 3

Thanks, Dave. I'm looking for more of a passive system where DBAs are only notified of errors. In implementing the Hallengren scripts for database maintenance for many servers and locations, one script creates the jobs and supporting procedures, another I put together schedules all the jobs, and all I have left is setting up e-mail notification if any jobs fail.

Lowell SSC Guru Points: 323518 More actions · Answer 4

Bryan i think you can simply add a step to your job that would do the notification, and then change the On Failure to call that notification step instead, right?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Pam Brisjar SSChampion Points: 12094 More actions · Answer 5

Lowell, he wanted it scriptable...

Here's where to check the job stuff:

msdb.dbo.sysjobs

msdb.[dbo].[sysoperators]

So in essence you'd get the data from these tables then run the above SP to add the notify. There's another sp to add an operator if you don't have one.

Of course you still need to verify that Agent is properly configured to send mail (and restarted once configured). Haven't found the table for that yet.

Bryan Welch-438459 Old Hand Points: 372 More actions · Answer 6

Found it. Here are the generated scripts for Hallengren's CommandLog Cleanup job. Note this assumes you have Database Mail already configured with an operator called 'dba'.

-- Without notification set:

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job

@job_name=N'CommandLog Cleanup',

@enabled=1,

@notify_level_eventlog=2,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'Source: http://ola.hallengren.com',

@category_name=N'Database Maintenance',

@owner_login_name=N'sa',

@job_id = @jobId OUTPUT

-- With notification set

EXEC @ReturnCode = msdb.dbo.sp_add_job

@job_name=N'CommandLog Cleanup',

@enabled=1,

@notify_level_eventlog=2,

@notify_level_email=2,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'Source: http://ola.hallengren.com',

@category_name=N'Database Maintenance',

@owner_login_name=N'sa',

@notify_email_operator_name=N'dba',

@job_id = @jobId OUTPUT

-- Difference:

@notify_level_email=2 instead of @notify_level_email=0

@notify_email_operator_name=N'dba' instead of null

-- To add e-mail notification, run this:

use msdb

EXEC dbo.sp_update_job

@job_name = 'CommandLog Cleanup'

, @notify_level_email = 2 -- on failure

, @notify_email_operator_name ='dba'

wingman2468 SSC Journeyman Points: 95 More actions · Answer 7

--To update all jobs that do not have email notification enabled I run this:

DECLARE @jobid AS NVARCHAR(MAX)

DECLARE jobstep_cursor CURSOR FOR

SELECT job_id

FROM msdb.dbo.sysjobs

WHERE enabled = 1 AND notify_level_email = 0

OPEN jobstep_cursor;

FETCH NEXT FROM jobstep_cursor INTO @jobid;

WHILE @@FETCH_STATUS = 0

BEGIN

EXEC msdb.dbo.sp_update_job @job_id = @jobid,

@notify_level_email=2,

@notify_level_netsend=2,

@notify_level_page=2,

@notify_email_operator_name=N'SQL Admin'

FETCH NEXT FROM jobstep_cursor INTO @jobid;

END

CLOSE jobstep_cursor

DEALLOCATE jobstep_cursor