Sending mail messages from SQL Server


By:   |   Updated: 2020-04-06   |   Comments   |   Related: More > Functions - System

Problem

I need my SQL Server T-SQL program to send an email message. What options are available to send email from SQL Server?

Solution

SQL Server provides 2 stored procedures to send email messages.  They are sp_send_dbmail and sp_notify_operator.  Both stored procedures are stored in msdb.  The procedure sp_send_dbmail is far more powerful and has many more parameters, 24 vs 7 respectively.  The procedure sp_notify_operator is much simpler to use, but lacks some of the powerful features of the former.

This tip will cover the ins and outs of each.  The procedure sp_send_dbmail is far more popular so the bulk of the time will be spent on it.

How sp_send_dbmail and sp_notify_operator work

The procedure sp_send_dbmail doesn’t actually send messages.  Its only goal is to create a message object with all of the fields one would type in when sending an email message by hand and put it into a service broker queue in msdb for the mail service to pick up.  When it successfully does this, the id of the object in the queue is returned.

The big difference with sp_notify_operator is that this procedure demands exactly one SQL Server Agent Operator and sends the message to that operator.  It does not offer many of the features traditional email would such as cc and bcc for recipients and formatting options.  The procedure actually just looks up the email address of the operator and then calls sp_send_dbmail with mostly default parameter values.

Parameters for sp_send_dbmail

The parameters will be described in groups.

The first parameter is @profile_name and it is not required.  This is the name of the mail profile to be used to send the message.  The procedure will use the default mail profile if not included in the call.  If there is not a default profile, then this becomes a de facto required parameter.  The data type is sysname or nvarchar(128).

The next group of parameters are traditional email fields.  While none of the recipient address lists are explicitly required, at least one of them must be included for the message to have anywhere to go.

Name Type Notes
@recipients  VARCHAR(max) Separate multiple recipients using a semi-colon (;)
@copy_recipients VARCHAR(max) Separate multiple recipients using a semi-colon (;)
@blind_copy_recipients VARCHAR(max) Separate multiple recipients using a semi-colon (;)
@from_address VARCHAR(max) This optional parameter can be used to attempt to override the mail profile.  Some mail servers may not allow this to be utilized.
@reply_to VARCHAR(max) This optional parameter can be used to attempt to override the mail profile.  Some mail servers may not allow this to be utilized.
@importance VARCHAR(6) This optional parameter defaults to "Normal".  It can also be "Low" or "High".
@sensitivity VARCHAR(12) This optional parameter defaults to "Normal".  It can also be one of "Personal", "Private", or "Confidential".

This next group of parameters are traditional email message fields.

Name Type Notes
@subject  NVARCHAR(255) This optional parameter will default to "SQL Server Message" if not included in the call.
@body NVARCHAR(max) This is the email message.  It is optional and will send a blank email if not included.
@body_format VARCHAR(20) This optional parameter defaults to "Text", but can also be "HTML".  "Text" is a plain text message and HTML offers a host of formatting options.
@file_attachments NVARCHAR(max) This optional parameter allows the user to list a file to be attached to the message.  The path to the file must be absolute and the SQL Server service account must have access to the files.  By default, there is a 1MB limit on any individual attachment.
Multiple files may be attached using a semi-colon (;) to separate them.

The final group of parameters has to do with running a query during execution and will be covered in depth later in the tip.

Parameters for sp_notify_operator

The parameters @profile_name, @subject, @body, and @file_attachments work exactly the same as sp_send_dbmail information written above as they are simply passed through to sp_send_dbmail.

The parameter @mail_database is not in use and should not be included in a call to sp_notify_operator.

This procedure does not ask for a to, cc, or bcc group of recipient email addresses.  Instead, it demands either the ID or the name of a SQL Server Agent Operator by including @id or @name.  @id is an INT and @name is an NVARCHAR(128).

Including a SQL Server query in email message

Perhaps the greatest feature of sp_send_dbmail is the ability to run a query and include those results in the email message.  This is accomplished by using another group of parameters.

The first 2 are the only ones required and they are @query and @execute_query_database.  @query is an NVARCHAR(max) and contains the TSQL statement to be executed.  The actual query execution happens in a separate process so this statement will not be able to reference any variables, table variables, or temp tables that might exist in the TSQL that is calling sp_send_dbmail.  The parameter @execute_query_database is a sysname, or NVARCHAR(128), and should contain the target database context in which to run the query.

Consider this sample execution.  Notice that it contains both a body and a query and that the query results appear after the body message.  The body is optional and without it the email will contain only the results of the query.

exec sp_send_dbmail @recipients = '[email protected]', 
  @subject = 'my test message', 
  @body = 'Please see this list of databases', 
  @query = N'SELECT name from sys.databases WHERE database_id < 5', 
  @execute_query_database = N'master'

With this execution the following arrived in the appropriate inbox.

This screenshot shows an email in an inbox with the message "Please see this list of databases followed by a plain text output of the query.

That works great for smaller messages, but can become cumbersome to read when the output is larger.  In those cases, consider this next parameter which will take those query results and move them into a plain text attachment to the email.  The parameter is @attach_query_result_as_file which is a bit.  Setting it to a 1 will change the query results to an attachment.

exec sp_send_dbmail @recipients = '[email protected]', 
  @subject = 'my attachment test message', 
  @body = 'Please see this list of databases', 
  @query = N'SELECT name from sys.databases WHERE database_id < 5', 
  @execute_query_database = N'master', 
  @attach_query_result_as_file = 1

With this execution the following email arrived in the appropriate inbox.  The file name of the attachment is random.

This screenshot shows an incoming email with an attachment named "QueryResults253393.txt".  The content of that attachment is the same plain text output as the previous message body.

This next group of optional parameters allow the caller to further tune this email attachment process.

Name Type Notes
@query_attachment_filename NVARCHAR(255) This parameter allows the caller to select a file name for an attachment rather than let the mail service select a random name.  It is ignored unless @attach_query_result_as_file has been set to a 1.
@query_result_header BIT This parameter can be set to 0 to suppress column headers in query results messages.  It works for both message-based output and attachments.
@query_result_width INT The mail service will insert line breaks within a single row of data every 256 characters.  This only comes in to play on wider result sets.  To override this action, include this parameter with a value between 10 and 32767 to indicate a desired character length before a line break is inserted.
@query_result_separator CHAR(1) Multiple columns in a result set are separated by a single space.  To override the space, include this parameter with a preferred separator.  This can be very valuable when a non-human will be reading the output.  It works the same for both message-based output and attachments.
@exclude_query_output BIT By default, the procedure sp_send_dbmail will output the id of the mail message in the mail queue.  Suppress that message by setting this parameter to 1.
@append_query_error BIT If the value for the @query parameter has an error then sp_send_dbmail will fail, not send a message, and return an error.  That is unless @exclude_query_output = 1 in which case it will not return an error but still won’t send the message.  If a message must be sent anyway then setting @append_query_error = 1 will tell the procedure to send a message and include the query’s error output rather than result set. 
@query_no_truncate BIT By default, large text and binary columns (often called MAX columns) are truncated at a maximum of 256 characters.  There will be no indication in the query output that truncation has or has not occurred.  Override this behavior by setting this parameter to 1 and the entire value will be included.
@query_result_no_padding BIT Including this parameter with the value of 1 will make the output of text columns appear as though they were called with a RTRIM function on them.  It can be a great way to make queries appear more human readable.
This parameter should not be combined with @query_result_width.

Best practices when using sp_send_dbmail or sp_notify_operator

The biggest mistake this author sees when people send messages from SQL Server is sending to them to personal accounts.  As a rule, always send messages to mailing lists.  This way if personnel change roles or leave the company there aren’t 100 stored procedures out there (in development and again in production perhaps) with their email address or their own personal operator on them that need to be changed.

If a mailing list cannot be made for whatever reason, make one using Agent Operators.  sp_notify_operator uses them by default and there is no reason sp_send_dbmail commands cannot be preceded with a query against the list of operators to get the current recipient list.

SELECT email_address FROM msdb.dbo.sysoperators WHERE [name] = N'DBA On Call';
Next Steps


Last Updated: 2020-04-06


get scripts

next tip button



About the author
MSSQLTips author Eric Blinn Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.

View all my tips





Comments For This Article





download


Recommended Reading

Concatenate SQL Server Columns into a String with CONCAT()

SQL Server Rounding Functions - Round, Ceiling and Floor

SQL Server 2016 STRING_SPLIT Function

Using FOR XML PATH and STRING_AGG() to denormalize SQL Server data

Different ways to get random data for SQL Server data sampling





get free sql tips
agree to terms


Learn more about SQL Server tools