December 20, 2018 at 9:34 am
We have multiple DB mail profile created on SQL server 2008 version server which are using for different applications.
We enabled one of the application job to get email alert whenever the job fails but we are receiving from wrong email account which is not ours and is using different application profile to send mail.
As multiple application are present on the SQL instance and DB profiles are configured ,apps team want their particular profile /email to receive alerts whenever the jobs fails.
Please let us know how to configure ?
Thanks.
December 20, 2018 at 10:19 am
When you configure email alerting for the job agent, you specify a particular profile to use. This is the profile that all job notification failures will use to send failure notification emails, so the "FROM" email address will always be the one associated with that selected profile.
Who receives the email can be configured by creating different Operators. Each Operator is set up with an email address, and then you associate the job failure email notification with the Operator you want to receive the notification.
You can send custom notifications from within your job itself using the DBMail system procedures. That would allow you to specify the profile used to allow for a specific "FROM" email address, but it wouldn't work for job failure notifications without some really weird hacks (configuring each step to jump to a separate email step on failure, and configuring the last step before the email step to exit on success) and even then it wouldn't be 100% reliable as the job could fail on the email step itself.
December 20, 2018 at 10:45 am
I forgot to mention that in most cases adding code within your job to send out email notifications means hard-coding email addresses in your job.
However, there is a way to fish out the job_id of the currently running job from within the job step itself, and use that to query the job notification settings using the MSDB job tables and fish out the email address of the notification operator set to receive job failures, and use that to send your custom email notification.
This at least gets around hard-coding the email address of the recipient, allowing you to change who is notified by changing the on failure notification setting of the job.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply