SQL Server Agent Job Notify multiple operators on failure

Question

I have a Job setup in SQL Server 2008 which sends a notification email to one operator when the job fails.

Question: Is it possible to setup a notification email being sent to multiple operators for that specific job?

I believe a possible workaround for this is to create lots of alerts for the database for each given severity but I was hoping that there was a more concise way to do this. If I were to go this route, what severity errors would likely be triggered from a failed job? (I don't think I would require all 25 for something like that)

Can this be done through sql command to add more operators to notify on failure? Through the UI you are only able to choose a single operator it seems.

Menedez

IIRC you can define individual operators with multiple email addresses if that's any good to you? – Janusfaced

No I have that but the issue is that you can only select a single operator in the notification section of the job – Menedez

Yes I don't think there's a way of doing what you want then. When I've wanted to alert multiple people I've just set up a new operator as I recall. [msdb].[dbo].[sysjobs] has columns for operator_id rather than them being in a separate table that would support 1 to many. – Janusfaced

Okay I can try that out and see if it does work, I misread your comment the first time sorry – Menedez

I didn't know that you were able to add multiple addresses, that is such a simple solution thanks! If you write it as an answer I can mark this as answered – Menedez

Will do. Can you remind me is it a semi colon delimited list? – Janusfaced

Yes it is a semi colon delimited list – Menedez

Although this question is totally inappropriate here (because it is not a programming question), I had to upvote it (and some of its answers), because it saved me a lot of time and frustration. – Honshu

Answer 1

Question: Is it possible to setup a notification email being sent to multiple operators for that specific job?

I don't believe this is possible.

Certainly looking at the structure of [msdb].[dbo].[sysjobs] the various operator_id columns are in this table itself which would support the idea that 1 to many is not possible.

But some alternatives

1. You could create a new operator with the semi colon delimited list of email addresses. Looking at the definition of sysoperators this is good for strings that can fit in nvarchar(100)
2. if you need to exceed that you could probably set up an email distribution group on exchange or whatever.

Janusfaced

We were using the email group method (Exchange Distribution list) where I work, until our security policy for DLs was changed on the Exchange side to "Require that all senders are authenticated". Notifications to individual email addresses still works, so this has been a handy (hopefully temporary) workaround. 100 Characters is not a whole lost of space though. To give an idea, just 3 email addresses brought us to 56 characters. – Deluxe

please see answer below, if this is true, please delete this answer, EXECUTE msdb.dbo.sp_update_operator @email_address = N'[email protected];[email protected]'; – Zante

@apple this is the same as the answer below. The "not possible" is referring to multiple operators not multiple email addresses. – Janusfaced

Answer 2

If the intention is that multiple people in your organization should be notified if a job fails, you can change the email address of the operator to include multiple mailboxes by separating each mailbox with a semicolon.

I'm assuming your notified operator is called JobWatcher:

EXECUTE msdb.dbo.sp_update_operator
  @name = N'JobWatcher',
  @email_address = N'[email protected];[email protected]';

Now [email protected] and [email protected] will receive mail when the job fails.

Dulin

This is correct although has someone has already stated the field only allows 100 characters which soon fills up with multiple addresses. – Gosling

@MarkHedley it is a short-term solution, but if it is difficult for you to make changes to groups on your mail server, it might still be useful. I tried to update the answer, but SO is erroring just now. I'll try again later. Thanks for the feedback! – Dulin

A nasty by-product of the 100 character issue is that it'll let you put more than 100 characters, but then cut-it-off after you hit OK! – Lucylud

Answer 3

The simplest method i use to notify multiple "OPERATORS" on "JOB FAILURE" is to:

In SSMS>SQL Server Agent>Operators create a new OPERATOR or EDIT existing and add additional email addresses separated by ; in "E-mail name:" box.

Congratulation

this needs to be WAY HIGHER UP – Weismannism

@mmcrae or you can learn to read. The answer you just downvoted contains the line You could create a new operator with the semi colon delimited list of email addresses – Janusfaced

@MartinSmith - this one's still more clear imo ("you could learn to read" - classy!) Your answer is more focused on the question's literal query (about operators), but it's clear the OP is just looking for an easy way to email multiple addresses... and this is that easy way. – Weismannism

Didn't work as my list of emails was longer than 100 characters. Even after expanding the column width of the 'email_address' column to be able to accommodate multiple email addresses (if total length > 100 chars), using: USE msdb; ALTER TABLE sysoperators; ALTER column email_address NVARCHAR(2000); This rendered the SSMS GUI useless as it won't open the "Notification" tab/dialog-box under "Agent-Job Properties". I had to go with T-SQL approach (by adding additional steps to jump to "on success/failure action"). – Gumm

Answer 4

The best practice would be to create a group on your mail server, send the notifications to the group and then control the number of recipients from the mail server.

Recycle

This is how we do it where I work. Say all your admins are part of an Outlook distribution group called DBAs. You would create an agent operator called 'DBAs' with the email address '[email protected]' to notify everyone in the distribution group. – Dulin

Answer 5

So this is what I came up with as a workaround if the intention is that multiple people in your organization should be notified if a job fails and a different group of multiple people for successes.

You will notice the Steps 1-3 are the normal tasks that the schedule job is uses for as you would do for your task. There can as many steps as needed before these but the last step (Step 3) of the process need to break “On Success” and “On Failure” to go into separate emails. Also all “On Failures” need to continue to your “Failure Email” as highlighted below. So the Failure group gets there emails and the job will still fail for the historical records.

You will see the option to change the direction of the “On success action” and “On Failure action” in the Advanced tab of the Job steps.

Failure Email Step -General Property

Failure Email Step -Advance Property

Success Email Step -General Property

Success Email Step -Advance Property

For others in need help. Notify multiple operators with difference results

Jeaniejeanine

While dbahiker doesn't directly address the problem that the original question asks, I think it's underrated (at -1). It's a viable solution to the limits of SQL Agent Operators. – Complexioned

Yeah, this is a great workaround (to a limitation that serves no purpose). – Capping

Answer 6

Please use below script to increase the character length of email address. USE mdsdb GO ALTER TABLE sysoperators ALTER column email_address NVARCHAR(2000);

Botanize

Thanks for that one and plus one. Finally, nobody should run into that limitation any more. The only thing (probably) is when SQL server is upgraded. I am not sure if it will reduce the length again, thereby cutting the strings, of even if the upgrade will fail. – Honshu

Even after expanding the column width of the 'email_address' column to be able to accommodate multiple email addresses (if total length > 100 chars), using: USE msdb; ALTER TABLE sysoperators; ALTER column email_address NVARCHAR(2000); This rendered the SSMS GUI useless as it won't open the "Notification" tab/dialog-box under "Agent-Job Properties". – Gumm

Answer 7

The question was asked 11 years ago of SQL Server 2008

Fast forward to 2023, even in SQL Server 2016 you can setup individual operators, and on any Alert you can specify multiple operators.

Altogether