How to Set Up Database Mail And Send An Email Notification When SQL Job Fails

Introduction:
This article explains how to set notification options in SQL Server  by using SQL Server Management Studio, Transact-SQL, or SQL Server Management Objects, so Microsoft SQL Server Agent can send notifications to operators about jobs.

Sometimes we create a job and there are many steps in the job, but if some steps fail then we need to notify the team or the user to check the cause of the error.

Now let us see how to notify the user that the job failed. I’ll list all the steps here so it can be done quickly and easily.

Here are the steps:

1. Configure Database Mail.

2. Create a SQL Server job.

3. Adjust the properties within the SQL Server Agent.

4. Create an Operator.

5. Adjust the SQL Server job to send on Failure.

Step- 1

Create a database mail profile to send an email. To do that please refer to here.

Step- 2

After creating the database email enable the SQL Server Agent to send an email.

Right-click on SQL Server Agent, select Properties and click Alert System.

•     Select the “Enable mail Profile” check box.

•     Select the Mail system as “Database Mail” from the drop down.

•     Select the database mail profile name.

•     Select the “Include body of e-mail in notification message” check box and click OK.

Step- 3

Now create an operator. An operator is whom you want to send an email notification.

Expand the SQL Server Agent node, right-click on operator and click New Operator.

Enter the operator name.

Enter the E-mail name (if you want to send an email to multiple people then you can use “;” as the separator).

Step- 4

Now create the job to send the email to the operator if the jobe fails.

Right-click on the Jobs node and select New Job.

Enter the name and click on Steps.

Create a step and select the failure action as “Quit the job reporting failure”.

Now click on “Notifications” and select the Email check box.

Select the operator that is created above.

Select “When the job fails”.

Now click on OK.

The job has been created and if the job fails then we will get an email notification with the error message.