Add e-mail notification to job via TSQL? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Add e-mail notification to job via TSQL?

Author

Topic

bryan42
Starting Member

28 Posts

Posted - 2011-11-15 : 12:56:11

Assuming e-mail is configured for a SQL Server, how may I use SQL to add e-mail notification to an existing job?

I heard I could use sp_add_notification, but this doesn't work for me as it doesn't recognize the alert name:
exec msdb.dbo.sp_add_notification
@alert_name = 'DatabaseBackup'
,@operator_name = 'dba'
,@notification_method = 1

When I generate scripts for an existing job with e-mail notification, I see this:

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'

What should I be doing?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-15 : 13:05:04

I usually use sp_send_dbmail in an error step.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-15 : 13:19:05

Think I see what you are trying to do.
You need to add the alert first - sp_add_alert - then you can reference it in sp_add_notification.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bryan42
Starting Member

28 Posts

Posted - 2011-11-15 : 14:33:50

Using SQL Server Management Studio, if I open the properties for a database backup job, I can select the 'notification' tab, enable it, and have one e-mail sent out if the job fails. I'm trying to do that GUI process in SQL.

If I enable e-mail notification for a job using the GUI, there are still no alerts shown for that database. Is there another way?

bryan42
Starting Member

28 Posts

Posted - 2011-11-16 : 13:37:33

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'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources