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.
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.
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.
|@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:
exec xp_sendmail @recipients = 'user@SentryOne.com', …
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';
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 = ',';