In this article, we will talk about sending email from SQL Server agent jobs by making use of operator and database mail feature. Before we can continue, make sure database mail is already configured. If database mail is not configured, please configure this feature by visiting here.
Below are the steps to be followed to send email from SQL Server agent Jobs: -
1) Connect to the database instance from SQL Server Management Studio and navigate to SQL Server Agent à Operator. Right click on Operator and click on ‘New Operator’
2) In this new window, provide the new operator name that you want to create. Also, provide the email address, which should appear as sender name, when email is sent to recipients. Avoid using net send and pager, as these features will be removed from future release.
3) Here in the Notification tab, you can select the appropriate options if you want to get notified for any alerts. Else click ok to save this new operator.
4) Now navigate to Jobs and go to the properties of SQL Server agent job for which you want to get notified via email.
5) Here go to the Notification tab and then select the check box against e-mail. Then select the operator we have created in previous step. You can also opt for sending this email when job completes or it runs successfully or it fails. Chose the appropriate option as per your requirement and click OK.
6) Just to verify, you can go back to Operators and select the properties of the Operator that we have used in above job.
Here go to notification tab and you will see that Jobs radio button is now enabled. When you select this radio button, you can see all the jobs, which are using this operator.
Also, you can go to the history tab of this operator to check when was the last time, email was sent by this operator.
7) If you have multiple database mails configured on this server and want to make sure that the operators to send email for SQL Server agent jobs use correct profile. Then navigate to SQL Server agent and go to the properties of SQL Server agent.
Here go to the Alert System tab and make sure that Enable mail profile option is enabled. Against mail system, select Database mail and against Mail profile, select the database mail profile that is to be used. Then click OK. SQL Server agent is to be restarted in order for above changes to take effect.
Now making use of the database mail profile selected above will send all emails from SQL Server agent operator.