CODEWITHSUNDEEP
sql-serversql-server-2008alertsql-server-agent
Mike
Mike

Reputation: 8545

SQL Server Agent Job Notify multiple operators on failure

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.

Upvotes: 38

Views: 80339

Answers (7)

Jason S
Jason S

Reputation: 1544

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

Upvotes: 1

JERRY
JERRY

Reputation: 1173

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

Upvotes: 5

Martin Smith
Martin Smith

Reputation: 452978

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.

Upvotes: 42

dbahiker
dbahiker

Reputation: 193

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

Upvotes: 13

Sagheer Ahmed
Sagheer Ahmed

Reputation: 441

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.

Upvotes: 34

Mackin
Mackin

Reputation: 131

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.

Upvotes: 13

Iain Samuel McLean Elder
Iain Samuel McLean Elder

Reputation: 20924

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.

Upvotes: 35

Related Questions