sp_sentry_dbmail_20

Introduction

When a SQL Server 2005 and above instance is first set to watched status with SQL Sentry, SQL Sentry auto-installs a stored procedure in the MSDB database named sp_sentry_dbmail_20  that is intended to be an interface compatible replacement for sp_send_dbmail. sp_sentry_dbmail_20 that supports all functionality of sp_send_dbmail  without the need to configure each server to use it. Therefore, the configuration and maintenance requirements are dramatically reduced over sp_send_dbmail.

After watching the SQL Server 2005 and above instance, all that's required is replacing any calls to sp_send_dbmail with sp_sentry_dbmail_20 shown as follows:

sp_sentry_dbmail_20 Parameters

A list of sp_sentry_dbmail_20 parameters is shown in following the table. All parameters are optional except for @recipients. You must specify at least one of @body, @query, @file_attachments, or  @subject. Otherwise, sp_sentry_dbmail_20 returns an error. Parameters are identical to those used by sp_send_dbmail.

Parameter	Description
@profile_name	Profiles are required to be set up in SQL Server 2005 and above before using mail functionality. Profiles aren't required for SentryOne; therefore, this parameter is optional for sp_sentry_dbmail_20.
@recipients	Email addresses of recipients, delimited by semicolons.
@copy_recipients	Email addresses of recipients to be copied, delimited by semicolons.
@blind_copy_recipients	Email addresses of recipients to be blind copied, delimited by semicolons.
@subject	Subject line of the message.
@body	The body of the message.
@body_format	The format of the message body. May be TEXT or HTML.
@importance	The importance of the message. May be Low, Normal, or High.
@sensitivity	The sensitivity of the message. May be Normal, Personal, Private, or Confidential.
@file_attachments	A semi-colon delimited list of files to attach to the message.
@query	A SQL statement to be executed. The results of the query is included in the body of the email by default, or as an attachment if @attach_query_result_as_file = one.
@execute_query_database	The name of database on which to run the query.
@attach_query_result_as_file	If one, the query results are attached as a text file. If zero (default), the results are included in line in the body of the message.
@query_attachment_filename	The file name to use for the result set of a query when attached. If none is specified and the above parameter =one, an arbitrary file name is used.
@query_result_header	If one(default), the query results is included in the column headers. If zero, they aren't included in the column headers.
@query_result_width	The line width, in characters, for rows in the query output.
@query_result_separator	Column separator value to use for the query output. The default is ' ' . (space)
@exclude_query_output	If one, no output is returned to the client when the message is sent. If zero (default), Mail sent is returned to the client
@append_query_error	If one, the message is sent with the query error in the body. If zero (default), the message isn't sent.
@query_no_truncate	If one, query results aren't truncated. If zero (default), columns truncate to 256 characters.
@mailitem_id	Optional output parameter returns the mailitem_id of the message. The mailitem_id is of type int.
@from_address	The mail from address to use for the message.

Replacing Calls To Sp_send_dbmail

Change calls to sp_send_dbmail to msdb..sp_sentry_dbmail_20, shown as follows:

Old Call:

exec sp_send_dbmail @recipients = 'user@sentryone.com', …

New Call:

exec msdb..sp_sentry_dbmail_20 @recipients = 'user@sentryone.com', …

Examples

Results Embedded in Message Body

exec msdb..sp_sentry_dbmail_20 
@body = 'Products selling for over $2500.',
@body_format ='Text',
@from_address = 'm@sentryone.com', 
@recipients = 'e@sentryone.com', 
@copy_recipients = 'm@sentryone.com',
@subject = 'Results Embedded in Message Body',
@query = 'SELECT ProductID, [Name], ProductNumber FROM Production.Product WHERE ListPrice > 2500',
@execute_query_database = 'AdventureWorks2014',
@attach_query_result_as_file = 0;

Results:

Comma-Separated Results Attached to Message

exec msdb..sp_sentry_dbmail_20 
@body = 'See attached results.',
@body_format ='HTML',
@from_address = 'm@sentryone.com',
@recipients = 'e@sentryone.com', 
@copy_recipients = 'm@sentryone.com',
@subject = 'Comma-separated Results',
@query = 'SELECT ProductID, [Name], ProductNumber FROM Production.Product WHERE ListPrice > 2500',
@execute_query_database = 'AdventureWorks2014',
@attach_query_result_as_file = 1,
@query_result_separator = ',',
@query_attachment_filename = 'queryresultscomma.txt';

Results:

Using in an Execute SQL action

Troubleshooting

Use the dbo.SQLSentryDBEmails_20 table in the msdb database to find the status of a message.

For example:

USE msdb;
SELECT *
FROM dbo.SQLSentryDBEmails_20
WHERE [status] != 4
ORDER BY send_request_date DESC;

[status]	Description
0	Ready for processing
1	Waiting for query results
2	Reserved
3	Retrieved by monitor
4	Email sent
5	Email failed
6	Message creation failed