How To Send Email From SQL Server With Step By Step Instructions.

This guide uses SQL Server 2014 Standard and the adventure works database

One of the main reasons for sending an email from SQL Server database is to report any errors that may occur when running jobs etc. 

This blog post will show you how to send emails from SQL server with step by step instructions.

Like most blogs that I write, let's jump straight in.

To start you must have a mail account as you will need to add your SMTP settings when you configure database with the SQL server agent.

Enable fail-safe operator.

To start we need to enable fail-safe operator, to do this, follow the instructions below.

  • Click on SQL Server Agent
  • Right Click on Operators and select 'New Operator'
  • Give the operator a name
  • E-mail name: enter the email address you want to use
  • Click OK
Operator

How To Enable Database Mail-In SQL Server

Once done, under management, right-click Database mail and select 'Configure Database Mail' as per the image shown.

Configure Database Mail

This will then show the 'Welcome to Database Mail Configuration Wizard' like the image below shows.

Welcome to Database Mail Configuration Wizard

Click next and you will see the 'Select Configuration Task', select Set up Database Mail by performing the following tasks:

Select Configuration Task

Click next and the next step will be 'New Profile', give the profile a Profile name, then click Add..

New Profile

Click next and then you will see the 'New Database Mail Account' window, enter an Account Name and then complete your SMTP server settings.

New database mail account

Once done click next and you will see the 'New Profile' window.

New Profile

Check everything is correct then click next and you will see the 'Manage Profile Security' window.

Manage Profile Security

Choose whether you are creating a Public or Private profile and then click next.

Configure System Parameters

Check over the parameters and click Next.

Complete the Wizard

Finally, click finish and the configuration wizard run and if everything goes OK, you should see the image below.

Configuring

Click close and then right-click database mail and click 'Send Test E-Mail.

Send test email

You will then see the following:

Database Mail Test E-Mail

If all goes to plan, you should receive an email like the image below shows.

Setup success

And that's how you configure database mail in SQL Server. So how do we use it to send an email if a job either fails or succeeds?

Send Email From SQL Server When Job Runs

Now right-click SQL Server Agent and then select properties, then click Alert System and under Mail Session, select the mail profile we created earlier.

You can now also select the 'Fail-safe operator' if required.

Mail Session

Now we can create a new job under SQL Server Agent, to do this, right-click SQL Server Agent and then click New then Job and give the job a name.

New SQL Server Agent Job

Then click Steps and then new.

New job step one

Give the step a name and then select the database, in this case, AdventureWorks and in command enter the SQL you want to run.

I'm just using the SQL code below:

BACKUP DATABASE [AdventureWorks] TO  DISK = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.WINDOWS10PRO\MSSQL\Backup\AdventureWorks.bak' WITH NOFORMAT, INIT,  NAME = N'AdventureWorks-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Then click advanced and under 'On success action,' select 'Quit the job reporting success'

Then click schedules and then new, give the schedule a name, in this case, I'm just using the setting already selected and then click OK.

Give the schedule a name and modify the settings, I'm just using the defaults for this blog.

Schedule name

I'm not using alerts in this blog, so you can skip over that and then click on Notification, select email and you should be able to select the DefaultOperater we created earlier.

Default Operator

Finally, click OK and then right-click the job created and then 'start job at..', this will run the job and you should receive an email as the image below shows:

Job run success

Creating and sending mail from an SQL server can be a lot more complicated, but I have tried to keep this blog as simple as possible, I hope you found it helpful if it's what you were looking for.