Notifying Multiple Recipients of Job Completion

Question

Post reply

Notifying Multiple Recipients of Job Completion

dbaforever

Hall of Fame

Points: 3740
More actions
September 28, 2008 at 12:47 pm

#126612

hi everyone,
I'm using 2005 Database Mail feature to send job completion emails.
Due to auditor requirements (ugh!), we must notify a certain email address of all job completions - whether successful or failure.
Presently, I have added my email address to the operator. That operator is notied of all job completions.
** But as DBA, I only want to receive notifications of job failures. **
2005 does not enable us to send failures to 1 address and all job completions to a different address.
What other options do I have and are you sending job notifications to more than 1 recipient? Which tool are you using?
Thanks - I hope my question is clear.
John

Viewing 5 posts - 1 through 4 (of 4 total)

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

Vivien Xing SSChampion Points: 12297 More actions · Answer 1

1. when job failure, go to this step:

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'AdventureWorks Administrator', -- use your own profile

@recipients = 'danw@Adventure-Works.com; yourname@email.com; othername@email.com',

....

or

2. ask the exchange server admin to create an alias to include all the emails need to get the notification. Use the alias for the notification email address.

dbaforever Hall of Fame Points: 3740 More actions · Answer 2

Thanks Vivien.

I was afraid my question was not clear.

I want to send only failure notifications to myself, but send ALL types of completions to another email addy. Dont think SQL Agent can do that. So I'm asking is there any other way to do that. We do use Exchange, as you presumed.

Thanks, John

ianT SSCrazy Points: 2956 More actions · Answer 3

why not have two extra steps - one for good end and one for failure?

Your 'normal' processing will complete and move to the good end step. this e-mails completion.

If there is an error in any step, move to the failure step which e-mails you and completes.

Look at the jobstep properties advanced tab - 'on success action' and 'on failure action' drop-downs.

EdVassie SSC Guru Points: 60448 More actions · Answer 4

Create a distribution list in Exchange, and use the name of the distribution list as the email address in your Agent Operator.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara