Documentation forSQL Sentry

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:

Note:  For sp_sentry_dbmail_20 to function, SMTP settings must be configured first.
Note:  You may receive an error if the required components are not turned on:

Msg 15281, Level 16, State 1, Line 26
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.
Additional Information: Microsoft documentation for Ad hoc distributed queries Server Configuration Option.

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:

SentryOne Results Embedded Message Body

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:

SentryOne Comma Separated Results

Using in an Execute SQL action

Additional Information: See the blog post Using SentryOne to Alert on SQL Server Database Object Changes for an example of using  sp_sentry_dbmail_20 parameters in a custom message for condition alerting.

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