sp_sentry_dbmail_20
Introduction
When a SQL Server 2005 and above instance is first set to watched status with SQL Sentry, SQL Sentry 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:
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.
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.
Parameter | Description |
---|---|
@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:
Old Call:
exec sp_send_dbmail @recipients = 'user@sentryone.com', …
New Call:
exec msdb..sp_sentry_dbmail_20 @recipients = 'user@sentryone.com', …
Examples
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:
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:
Using in an Execute SQL action
Troubleshooting
Use the dbo.SQLSentryDBEmails_20 table in the msdb database to find the status of a message.
For example:
USE msdb;
SELECT *
FROM dbo.SQLSentryDBEmails_20
WHERE [status] != 4
ORDER BY send_request_date DESC;
[status] | Description |
---|---|
0 | Ready for processing |
1 | Waiting for query results |
2 | Reserved |
3 | Retrieved by monitor |
4 | Email sent |
5 | Email failed |
6 | Message creation failed |