37

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.

Mike
  • 8,137
  • 6
  • 28
  • 46
  • IIRC you can define individual operators with multiple email addresses if that's any good to you? – Martin Smith Sep 15 '11 at 13:26
  • No I have that but the issue is that you can only select a single operator in the notification section of the job – Mike Sep 15 '11 at 13:26
  • 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. – Martin Smith Sep 15 '11 at 13:29
  • Okay I can try that out and see if it does work, I misread your comment the first time sorry – Mike Sep 15 '11 at 13:30
  • 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 – Mike Sep 15 '11 at 13:37
  • Will do. Can you remind me is it a semi colon delimited list? – Martin Smith Sep 15 '11 at 13:40
  • Yes it is a semi colon delimited list – Mike Sep 15 '11 at 13:43
  • 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. – Binarus Jan 09 '20 at 11:51

7 Answers7

42

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.
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 1
    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. – Voysinmyhead Mar 17 '16 at 18:31
  • please see answer below, if this is true, please delete this answer, EXECUTE msdb.dbo.sp_update_operator @email_address = N'person1@company.org;person2@company.org'; –  Feb 07 '18 at 04:03
  • @apple this is the same as the answer below. The "not possible" is referring to multiple operators not multiple email addresses. – Martin Smith Feb 07 '18 at 13:12
34

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'person1@company.org;person2@company.org';

Now person1@company.org and person2@company.org will receive mail when the job fails.

Iain Samuel McLean Elder
  • 19,791
  • 12
  • 64
  • 80
  • 4
    This is correct although has someone has already stated the field only allows 100 characters which soon fills up with multiple addresses. – Mark Hedley May 13 '15 at 10:51
  • @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! – Iain Samuel McLean Elder May 14 '15 at 12:22
  • 3
    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! – PeterX Oct 15 '15 at 23:19
33

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.

SilverlightFox
  • 32,436
  • 11
  • 76
  • 145
Sagheer Ahmed
  • 331
  • 3
  • 2
  • this needs to be WAY HIGHER UP – Don Cheadle May 18 '18 at 19:37
  • @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` – Martin Smith May 18 '18 at 19:40
  • @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. – Don Cheadle May 18 '18 at 20:42
  • 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"). – Eddie Kumar Jun 14 '21 at 16:48
13

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.

Mackin
  • 131
  • 1
  • 2
  • 1
    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 'DBAs@company.org' to notify everyone in the distribution group. – Iain Samuel McLean Elder Nov 13 '13 at 17:12
11

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.

1.1

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.

2

Failure Email Step -General Property

3

Failure Email Step -Advance Property

4

Success Email Step -General Property

5

Success Email Step -Advance Property

6

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

dbahiker
  • 173
  • 1
  • 10
  • 1
    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. – RyanB Nov 08 '16 at 22:40
  • Yeah, this is a great workaround (to a limitation that serves no purpose). – Flat Cat Dec 05 '22 at 20:32
5

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

JERRY
  • 1,165
  • 1
  • 8
  • 22
  • 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. – Binarus Jan 09 '20 at 11:54
  • 2
    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". – Eddie Kumar Jun 14 '21 at 16:45
1

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.

enter image description here

Jason S
  • 1,361
  • 20
  • 24