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
SQL Server provides a
- 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
"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
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.
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
-- 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
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
-- 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!
With the basics up and running, let's look at features and applications of this handy little tool:
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!
Using a SQL Agent, you can easily invoke the sp_send_dbmail stored proc from the agent's
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.
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!
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