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 = 'redacted@redacted.xyz', @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.
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 = 'redacted@redacted.xyz', @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 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
