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_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.
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.
|@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:
exec sp_send_dbmail @recipients = 'email@example.com', …
exec msdb..sp_sentry_dbmail_20 @recipients = 'firstname.lastname@example.org', …
Example: Results Embedded in Message Body
exec msdb..sp_sentry_dbmail_20 @body = 'Products selling for over $2500.', @body_format ='Text', @from_address = 'email@example.com', @recipients = 'firstname.lastname@example.org', @copy_recipients = 'email@example.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;
Example: Comma-Separated Results Attached to Message
exec msdb..sp_sentry_dbmail_20 @body = 'See attached results.', @body_format ='HTML', @from_address = 'firstname.lastname@example.org', @recipients = 'email@example.com', @copy_recipients = 'firstname.lastname@example.org', @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';