CODEWITHSUNDEEP
sql-serversql-server-2008sql-agent-jobsql-job
whatwhatwhat
whatwhatwhat

Reputation: 2276

SQL Server not sending email when job fails

I have a job with a step that runs the following code:

SELECT * FROM TABLETHATDOESNTEXIST

I have a SQL Server Agent Operator tied to my target email. I've set up the job so that when the job fails, it should email the Operator. However, when I run the job (and watch it fail), the email isn't sent.

I made sure to send a test email via exec sp_send_dbmail and also via Object Explorer > Database Mail > Send Test E-Mail and that works fine. What else am I missing here?

The job only has 1 step ("FRED" is a table that doesn't exist): enter image description here

These settings were the defaults I believe: enter image description here

The notifications tab of the job (the Operator here I selected is the same one everyone because there is currently only 1 Operator in the whole system): enter image description here

The Operator is pretty simple to set up: enter image description here

The Operator history shows that an email attempt was made, but I never received anything: enter image description here

Upvotes: 1

Views: 2623

Answers (1)

Lam
Lam

Reputation: 721

Besides configuring:

  • Management > Database Mail

  • SQL Server Agent > Operators

  • And adding a Notification in Job Properties to send an E-mail to the operator when a Job fails

You also need to configure SQL Server Agent through Properties > Alert System > Mail session

Then it should work, at least it does for me on SQL Server 2016.

enter image description here

Upvotes: 3

Related Questions