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.

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.

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.