Programming Projects: Sending Automated SQL Server Notification Emails

One of our clients asked today to be automatically notified through email if a failure in the regularly scheduled jobs in SQL Server occurs. This seems like a pizza cake (yummy!) considering that sending email is common and I'm sure I can find lots of information on the internet on how to achieve this in SQL Server.

The task was simple but unfortunately it took me the whole day to configure the Server. Information was available on the internet and I seem to be following the instructions correctly to the letter. But somehow, the feeling of success and being able to shout Yes! did not occur immediately and the simple task became a nightmare of trial and errors which left me wondering whether working in the office instead of working at home is better.

Now to the main course, in order for SQL Server to be able to send emails, the Database Mail must be enabled and configured. The Database Mail is available only for versions higher than the SQL Server Express Editions. First, you need to open SQL Server Management Studio and connect to the SQL Server Database. Once connected, go to the Object Explorer window and under the Management folder, right click Database Mail and select "Configure Database Mail".

When the initial screen shows, click Next.

In the "Select Configuation Task" Window, select "Setup Database Mail" option.

In the "New Profile" window, specify the Profile Name and Description in the designated text boxes and then click "Add".

In the "Add Account To Profile", select an account to add to the Profile you've created earlier or you can click "New Account" to create a new account.

When you create a new account, the "New Database Mail Account" window opens. specify the account name and description, then specify the the Outgoing Mail Server (SMTP) configuration and its authentication. Here, you need to be able to know the SMTP settings that your company uses. Then click OK. You will be taken back to the "New Profile" window earlier but with a new SMTP account specified. Click next.

In the "Manage Profile Security" window, click the Option to make your profile public and available. Click Next.

In the "Configure System Parameters" window, you can leave the default settings and click Next.

Then click Finish.

The Database Mail is now configured. You can check if you've configured it correctly by right-clicking on the Database Mail in Object Explorer and select "Send Test Email". Provide the email of the recipient and click Send. If the email is received, then pat yourself in the back because you're already halfway passed the work to be made.

We need to be able to use Notification Email in SQL Agent. To do that, you need to specify the "Operator" or the list of emails that can be the recipient of the email Notifications.
In the Object Explorer again, under the SQL Server Agent node, right click the folder Operators and select "New Operator".

In the "New Operator" window, specify the name of the Operator. Put the email recipients separated by semicolon in E-mail name text box. Click OK.

Then you need to enable the mail profile in SQL Server Agent properties. After enabling, restart the SQL Server Agent.

And finally, you can use the settings you've configured above in your SQL Server Jobs so that it can send email automatically if a job fails to execute. To do that, in the Jobs properties, under Notifications Tab,check email option and specify the operator you want to receive the email and select the event that can trigger the sending of the notification email.