sp_sentry_dbmail_20

When a SQL Server 2005 and above instance is first set to watched status with SQL Sentry, SentryOne auto-installs a stored procedure in the MSDB database named sp_sentry_dbmail_2that 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.

ParameterDescription
@profile_nameProfiles 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.
@recipientsEmail addresses of recipients, delimited by semicolons.
@copy_recipientsEmail addresses of recipients to be copied, delimited by semicolons.
@blind_copy_recipientsEmail addresses of recipients to be blind copied, delimited by semicolons.
@subjectSubject line of the message.
@bodyThe body of the message.
@body_formatThe format of the message body. May be TEXT or HTML.
@importanceThe importance of the message. May be Low, Normal, or High.
@sensitivityThe sensitivity of the message. May be Normal, Personal, Private, or Confidential.
@file_attachmentsA semi-colon delimited list of files to attach to the message.
@queryA 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_databaseThe name of database on which to run the query.
@attach_query_result_as_fileIf 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_filenameThe 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_headerIf one(default), the query results is included in the column headers. If zero, they aren't included in the column headers.
@query_result_widthThe line width, in characters, for rows in the query output.
@query_result_separatorColumn separator value to use for the query output. The default is ' ' . (space)
@exclude_query_outputIf 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_errorIf one, the message is sent with the query error in the body. If zero (default), the message isn't sent.
@query_no_truncateIf one, query results aren't truncated. If zero (default), columns truncate to 256 characters.
@mailitem_idOptional output parameter returns the mailitem_id of the message. The mailitem_id is of type int.
@from_addressThe 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', …

Example: 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

Example: 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