sp_send_dbmail: Send Mail from SQL Server? Here's how.

Sp_send_dbmail May Answer Your Reporting Problem

"Can you send us a report of this data every day?", asks the ever-eager, smiling business person as he knocks on your monitor to interrupt you.

Businesses rely on data, and typically that data lives in a SQL Database. Reporting the data can frustrate the most zen developer, for a variety of reasons.  Perhaps that developer is:

• A Non-SQL master without the chops necessary for querying database
• An under-privileged employee lacking access to the database
• On a camping trip, far removed from civilization (and the database)

While investment in architecture and reporting services (or quite simply with the wonderful Sqlbot) can solve these problems, SQL Server provides a mechanism for accomplishing this with the "sp_send_dbmail" stored procedure.

"Hot dang!" you say?

Easy to configure, this stored proc sends results to a list of email addresses, with several formatting and querying options. Using sp_send_dbmail, you can send emails such as:

• "Your job has completed. Nice work."
• "443 wonderful widgets created today. Here they are:"
• "The DB has been deleted. May God have mercy on your soul"

Combining this proc with SQL Agent, you can schedule reports to execute on a regular interval, giving you a bona-fide reporting infrastructure, all housed within SQL Server. Point it to your SMTP server, and away you go!

Setup

SQL Server provides a couple routes to setting up the email goodness:

• Simple Wizards walking you through the decisions
• Confusing SQL requiring some deeper know-how

While it may sound counterintuitive, I prefer using the SQL. It deepens my understanding of the various components involved, which is useful during setup (and diagnosing when things don't work correctly). Let's get started with SQL.

What I'm Skipping

Depending on your version and configuration of SQL Server, there may be some permission tweaks, random settings, and one blog even recommends (gasp) copying some binaries around beforehand. I'm staying away from all that, because everyone's got different security, policies, voodoo, etc. If you encounter errors during the execution of the SQL below, a quick Google search will usually reveal the cause (and hopefully the solution).

Full Disclosure - I'm Learning This Too

I should note that I've never used this proc before - I'm learning it as I create this blog post! First things first - I googled it. MSFT's documentation was at the top of the results, with the high level description:

"Sends an e-mail message to the specified recipients. The message may include a query result set, file attachments, or both. When mail is successfully placed in the Database Mail queue, sp_send_dbmail returns the mailitem_id of the message. This stored procedure is in the msdb database."

OK, that sounds simple enough. Let's fire up SQL Server and give it a try:

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'I dont know what this is',  
    @recipients = '<my email>',  
    @body = 'youve sent email from database.',  
    @subject = 'great job' ;  

and the result:

Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0
SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Database Mail XPs' by using sp_configure. For more information about enabling 'Database Mail XPs', search for 'Database Mail XPs' in SQL Server Books Online.

In my head, this triggers a deep foghorn blaring a somber tune of failure across an ocean bay. Time to roll up my sleeves and dig deeper.

Useful Tables

One thing I love about SQL Server's subsystems is you can usually find the SQL tables that underpin them. In other words, SQL Server is usually "eating it's own dog food". The Send Mail functionality is no different - the following tables in the msdb system database come into play, and are worth querying as you work through this exercise:

    -- Contains the bits necessary to interact with SMTP server
	select * from sysmail_account
    -- Contains useful logs to inspect if something goes wrong
	select * from sysmail_log
    -- Contains details of each email sent
	select * from sysmail_mailitems
    -- Contains flags, bits, and values that drive email behavior
	select * from sysmail_configuration
    -- Provides the identifier the sp_send_dbmail proc uses to trigger an email
	select * from sysmail_profile
    -- Associative table between sysmail_profile and sysmail_account
	select * from sysmail_profileaccount
    -- Contains configuration around the SMTP server used to send mail
	select * from sysmail_server

Enabling Database Mail

Before using the tables above, we need to ensure that Database Mail is enabled in msdb. The following SQL accomplishes that:

use msdb

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'Database Mail XPs', 1;  
GO  
RECONFIGURE  
GO 

Mail Account Setup

A mail account can be setup with a single SP execution. Note that I originally attempted to use Gmail as my SMTP provider, but struggled with it (and the resulting errors) for some time. Switching to Live.com resulted in success, so I'm using that here. You can also use your own SMTP server if you have one.

-- Create a Database Mail account  
EXECUTE msdb.dbo.sysmail_add_account_sp  
    @account_name = 'Live.com',  
    @description = 'Mail account for sending outgoing notifications.',  
    @email_address = '<your email>',  
    @display_name = 'Automated Mailer',  
    @mailserver_name = 'smtp.live.com',
    @port = 25,
    @enable_ssl = 1,
    @username = '<live.com username>',
    @password = '<live.com password>' ;  
GO

Result: records populated in sysmail_account and sysmail_server.

We also need to create a profile, and attach to the account:

-- Create a Database Mail profile  
EXECUTE msdb.dbo.sysmail_add_profile_sp  
    @profile_name = 'Notifications',  
    @description = 'Profile used for sending outgoing notifications using Live.com.' ;  
GO

Result: records populated in sysmail_profile.

-- Add the account to the profile  
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp  
    @profile_name = 'Notifications',  
    @account_name = 'Live.com',  
    @sequence_number =1 ;  
GO

Result: records populated in sysmail_profileaccount.

Send the Email

With the necessary bits in place, we can send a simple test email:

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'Notifications',  
    @recipients = '<recipient list>',  
    @body = 'youve sent email from database.',  
    @subject = 'great job',
	@from_address = '<from email>',
	@reply_to = '<from email>'

Result: mail queued up in the sysmail_mailitems table, and after a short time, you'll see either success or failure in the sysmail_log table.

Upon success, you should feel a wonderful endorphin rush when the email lands in your inbox!

What Now?

With the basics up and running, let's look at features and applications of this handy little tool:

Result formatting

Depending on what your requirements, and what will be "reading" the email (be it a human, or another system), there are several flags that allow you to fine-tune how the results are formatted in the resulting email. Results in body vs attachment, separators, whether to include headers, formatting as HTML, etc. Not only does your database volunteer information - it looks good when doing so!

Scheduled Job

Using a SQL Agent, you can easily invoke the sp_send_dbmail stored proc from the agent's SQL, and have a regular SQL report sent to email. This is an easy way to create valuable daily/weekly/monthly reports, containing either business data, or DB system health info. Watch your database's self-esteem grow as it delivers a daily report, an equal member of the team!

Trigger Based

If there's something you need to know as it happens, a SQL Trigger is a powerful option. Set it up on the table(s) in question, and when certain thresholds or activities happen, your trigger can send you an email. It's a database's cry for help!

When NOT to Use

While sp_send_dbmail is super handy for quick "admin reporting" or alerting functionality, I would recommend against using it for "non-admin" related functionality in your application. Sending emails to users (eg, "Forget Password") is something that other providers (Mandrill, SendGrid, MailGun) do much better. They can handle the volume, support more formatting options, and provide much better infrastructure and reporting around results. Don't send sp_send_dbmail to a high-capacity, dedicated email serving API's job.

SQLBot

While this handy proc works in a pinch, you will presumably eventually graduate beyond "just give me the data, it doesn't have to be fancy". When that day comes, I'd recommend looking at a tool like Sqlbot in combination with a powerful collaboration hub like Slack (you are using something other than email to run your business, correct?). Having dedicated channels for reports is a powerful way of monitoring the business. SQLBot opens the door to achieving this without having to climb down into the guts of SQL Server functionality. It also connects to non-SQL Server databases, which is a great way to scale reporting across multiple data stores and systems. SQLBot is free to signup, and affordable once you fall in love with it!

In Conclusion...

Now that you've seen sp_send_dbmail in action, you possess another tool in your SQL Server arsenal for getting the job done. I recommend playing with it for a bit to see what it can do, then keep it in your pocket for the next time SQL results are just beyond reach. With a little sp_send_dbmail love, they no longer have to be.    Happy databasing!

Shout outs - Photo by Connor Botts on Unsplash

Try SQLBot - It's Free!

Sign up for a free account on SQLBot to see how easy it is to get SQL reports into your Slack.  What are you waiting for, it's free!