sp_sentry_mail_20

When a SQL Server instance is first set to watched status with SQL Sentry, SentryOne auto-installs a stored procedure in the MSDB database named sp_sentry_mail_20 that's intended to be an interface compatible replacement for xp_sendmail. sp_sentry_mail_20 that supports almost all of the functionality of xp_sendmail, including sending query results as attachments.

Aside from using SMTP, not MAPI, sp_sentry_mail_20 is a standard stored procedure, not an extended stored procedure, so it doesn't require installation of any dll’s, scripts, or other components on every SQL Server. The configuration and maintenance requirements are dramatically reduced over xp_sendmail and other xp_sendmail replacements. In addition, because sp_sentry_mail_20 has no dependencies on MAPI or SQLMail, it's inherently more reliable than xp_sendmail.

After watching the SQL Server instance, all that's required is replacing any calls to xp_sendmail with sp_sentry_mail_20, shown as follows.

Note:  For sp_sentry_mail_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_mail_20 Parameters

A list of sp_sentry_mail_20 parameters is shown in the following table. All parameters are optional except for @recipients. Parameters are identical to those used by xp_sendmail, except for two new optional parameters, @from_address and @email_format.  

ParameterDescription
@recipientsEmail addresses of recipients, delimited by semicolons.
@messageThe body of the message.
@queryA SQL statement to be executed. The results of the query are included in the body of the email by default, or as an attachment if @attach_results is True.
@attachmentsA semi-colon delimited list of files to attach to the message. Currently ignored. Planned for future version.
@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.
@typeIgnored. used by xp_sendmail only, but provided for xp_sendmail compatibility.
@attach_resultsIf True, the query results are attached as a text file. If False (default), the results are included in line in the body of the message.
@no_outputIf True, no output is returned to the client when the message is sent. If False (default), Mail sent is returned to the client.
@no_headerIf False (default), no column headers are included in the query results.
@widthThe line width for rows in the query output. Zero (default) indicates no width limit should be used.
@separatorColumn separator value to use for the query output.
@echo_errorIgnored. used by xp_sendmail only, but provided for xp_sendmail compatibility.
@set_userThe user context under which to execute the query. Currently ignored. Planned for future version.
@dbuseThe name of database in which to run the query.
@from_addressThe mail from address to use for the message.
@email_formatAllowed values are text or html only.

Replacing Calls to xp_sendmail

Change calls from xp_sendmail to msdb..sp_sentry_mail_20, shown as follows:

Old Call:

exec xp_sendmail @recipients = 'user@SentryOne.com', …

New Call:

exec msdb..sp_sentry_mail_20 @recipients = 'user@SentryOne.com', …

Example: Results Embedded in Message Body

exec msdb..sp_sentry_mail_20
@from_address = 'm@SentryOne.com',
@recipients = 'e@SentryOne.com',
@copy_recipients = 'm@SentryOne.com',
@subject = 'Product List sp_sentry_mail_20 Results Embedded in Message Body',
@message = 'Product List Report',
@query = 'SELECT ProductID, [Name], ProductNumber FROM Production.Product WHERE ListPrice > 2500',
@dbuse = 'AdventureWorks2014'; 

Results:

SentryOne Results Embedded 

Example: Comma-Separated Results Attached to Message

exec msdb..sp_sentry_mail_20
@from_address = 'm@SentryOne.com',
@recipients = 'e@SentryOne.com',
@copy_recipients = 'm@SentryOne.com',
@subject = 'Product List sp_sentry_mail_20 Comma-Separated Results Attached',
@message = 'Product List Report',
@query = 'SELECT ProductID, [Name], ProductNumber FROM Production.Product WHERE ListPrice > 2500',
@dbuse = 'AdventureWorks2014',
@attach_results = 'TRUE',
@separator = ',';

Results:

SentryOne Comma Separated Results