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
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.
This will then show the 'Welcome to Database Mail Configuration Wizard' like the image below shows.
Click next and you will see the 'Select Configuration Task', select Set up Database Mail by performing the following tasks:
Click next and the next step will be 'New Profile', give the profile a Profile name, then click Add..
Click next and then you will see the 'New Database Mail Account' window, enter an Account Name and then complete your SMTP server settings.
Once done click next and you will see the 'New Profile' window.
Check everything is correct then click next and you will see the 'Manage Profile Security' window.
Choose whether you are creating a Public or Private profile and then click next.
Check over the parameters and click Next.
Finally, click finish and the configuration wizard run and if everything goes OK, you should see the image below.
Click close and then right-click database mail and click 'Send Test E-Mail.
You will then see the following:
If all goes to plan, you should receive an email like the image below shows.
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.
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.
Then click Steps and then new.
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.
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.
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:
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.