A very powerful, but often overlooked feature of SQL Server is the ability to send emails in T-SQL. This feature has the ability to send to multiple recipients, as well as attach data simply by just providing a query and the name you want for the file. Although it sounds simple, the setup and the built-in MS stored procedure can be both hard and intimidating to understand. The goal of this article is to provide a simple overview of how to set up the email feature as well as sending a simple email and attaching data as a text file.
Step 1: Setting up the Email Client
Before you can send emails in T-SQL, the server needs to know what account you are going to be sending from e.g. your email address.
To set this up, open SQL Server Management Studio (SSMS), expand the “Management” Tree folder, and right click on “Database Mail” and select “Configure Database Mail”.
When you get to the configuration wizard, do the following:
- Click next on the welcome page (if applicable)
- Choose the “Set up Database Mail by performing the following tasks” radio button and click next
- you might be prompted that the database mail feature was not configured and you need to do so. If that message pops up, click yes
- For the profile name, enter a name that you will remember to associate with your email address…think of this as a nickname e.g. “mattsEmail”
- Under the section of the window for SMTP accounts, click the “Add” button to the right
- Under the various fields in the next page, enter all your information needed.
- Note: To find out your SMTP server address, goto your email host page, and it should be listed in settings. If its hard to find, do a simple google search for “SMTP server for Gmail” or “SMTP Server for outlook” depending on who your provider is
- Once you have finished entering your profile information, click OK
- Click next on the main wizard to bring you to the Public profiles page
- On the public profiles page, check the “public” box if you want your profile to be accessible by other users on the server that are not sysadmins
- Note: You can also select “Yes” on the “Default Profile” drop-down box if you want this to be the default email account to send emails from
- Click next and you will be on the final configuration page. On this page, you can specify file extensions to exclude as attachments and the max file attachment size.
- Note: The default maximum file size is 1 MB, which is pretty small. I usually add an extra 0 to the max file size line to bump it to 10 MB
- Once you are done configuring the extras, click Next and Finish
Step 2: Testing the email profile/account
Once you have gone through this setup, it’s time to run a test email to ensure the account is setup correctly. To do this, right click on the “Database Mail” item on the explorer tree in SSMS and click “Send Test E-mail…” You will be presented with a very simple pop-up asking you for the address you want to send the test email to. Enter an address you can check and then click the “Send Test E-Mail” button.
The SQL Server email function is pretty quick and you should see your email get sent and received in no less than a minute. If you received the email, you are all set and are ready to go to the last step in this article. If you have not received an email in 2 minutes, right click on the database mail folder and go back to the configuration. Odds are an SMTP server is not correct, or the authentication mechanism chosen is not correct.
Step 3: Sending an Email in T-SQL
The procedure to send an email is “msdb.dbo.sp_send_dbmail”. This procedure contains all the parameters needed to provide an email TO, CC, & BCC list, as well as set the email subject, body, and attach data by providing a query. What really makes this powerful is the parameterization of all the inputs…Think about it! You can automate you stored procedures to send specific data to distribution lists, alert users when a long running job has finished processing, and offload the constant emails and phone calls you get asking when a report will be ready.
Before we get into the examples, we will break down the sp_send_dbmail procedure and go over the parameters that are relevant for this exercise.
sp_send_dbmail parameters (relevant ones)
- This is the profile name you set up in step 1 above when configuring the email client. If you have multiple email addresses that you would want to send from, you can use the profile to function as a nickname for your different email accounts e.g. “work_profile”, “dev_alerts_profile”.
- This is your “TO” list. You supply email addresses and separate multiple email addresses using a semi-colon e.g. “firstname.lastname@example.org; john_doe@joes_pizza.com”
- This is your “CC” list. You supply email addresses the same way you would in the TO list
- This is your “BCC” list. Same rules apply like the recipients and blind copy recipients
- This is the email subject.
- Your email message
- Flag indicating if you want to attach a query output as an attachment
- The query you want to attach
- Flag indicating if you want to include column headers in the attachment
- The name you want the attachment to be e.g. “sls_rpt.txt”
- This is the column delimiter
To illustrate how to use the sp_send_dbmail procedure, we will provide 2 examples. The first one sends to a simple distribution list, just formatting the email subject and body. The second example takes it a step further and attaches a query results-set as a file.
Example 1: Sending a simple email
@recipients = 'email@example.com; firstname.lastname@example.org'
,@subject = 'Daily Report'
,@body = '
Please note that today's report will be delayed.'
In this example above, you separate multiple recipients using a semi-colon. Other than that, the other parameters are pretty straight-forward
Example 2: Sending an email and attaching data
@recipients = 'email@example.com'
,@subject = 'test email'
,@body = 'test email message'
,@attach_query_result_as_file = 1
,@query = 'select top 10 * from AdventureWorks2014.person.address'
,@query_result_header = 1
,@query_attachment_filename = 'test.txt'
,@query_result_separator = '|'
In this second example, we queries 10 rows from the adventureworks database, called the attachment “test.txt”, indicated we wanted to include headers, and we wanted the delimiter to be a pipe.