sp_sentry_mail_20

When a SQL Server instance is first set to Watched status with SQL Sentry, SQL Sentry 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.

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.

Parameter	Description
@recipients	Email addresses of recipients, delimited by semicolons.
@message	The body of the message.
@query	A 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.
@attachments	A semi-colon delimited list of files to attach to the message. Currently ignored. Planned for future version.
@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.
@type	Ignored. used by xp_sendmail only, but provided for xp_sendmail compatibility.
@attach_results	If 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_output	If True, no output is returned to the client when the message is sent. If False (default), Mail sent is returned to the client.
@no_header	If False (default), no column headers are included in the query results.
@width	The line width for rows in the query output. Zero (default) indicates no width limit should be used.
@separator	Column separator value to use for the query output.
@echo_error	Ignored. used by xp_sendmail only, but provided for xp_sendmail compatibility.
@set_user	The user context under which to execute the query. Currently ignored. Planned for future version.
@dbuse	The name of database in which to run the query.
@from_address	The mail from address to use for the message.
@email_format	Allowed 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:

 

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: