DBMail - SystemEmail

Due to the implementation of contained users, executing dbo.sp_send_dbmail from the MSDB database was no longer possible.

This is utilised in some areas of Synergetic that would email, such as Pastoral Care and Action Centre.

As a result of this the following architectural change was made:

  • New tables in the media schema
    • media.SystemEmail

    • media.SystemEmailRecipient

    • media.SystemEmailAttachment

  • New Stored Procedures in the media schema

    • media.spiSystemEmail

    • media.sptSystemEmailSend

    • media.spxSystemEmailSend

Anywhere that msdb.dbo.sp_send_dbmail would have been utilised in the past (e.g. buttons on User Forms), the stored procedure media.spiSystemEmail replaces this

  • Should only use this to call - direct replacement for msdb.dbo.sp_send_dbmail
  • Very similar named parameters so should be easy to replace where msdb.dbo.sp_send_dbmail used - see examples below

Any calls to msdb.dbo.sp_send_dbmail in Synergetic have been replaced with the new stored procedure.

User created objects (e.g. table triggers, stored procedures) that refer to msdb.dbo.sp_send_dbmail will need to be updated

How it works:

  • zSynergetic_main_dbo executes stored procedure media.spiSystemEmail
  • Stored procedure media.spiSystemEmail inserts a record in to the table media.SystemEmail and inserts all email recipients in to the table media.SystemEmailRecipient
  • A new SQL agent job "Synergetic Email Processor : {Synergetic DB Name}" runs every 1 minute by default (note this delay as sending could be delayed by up to this set amount)
    • Executed by zSynergetic_AUVIC_CDA_TST_ServerLogin (which is not a contained user) - only this user will send if security is wanted to be improved
      • zSynergetic_AUVIC_CDA_TST_ServerLogin can execute the stored procedure and access the MSDB database
    • Synergetic Email Processor... job runs media.spxSystemEmailSend
  • Stored procedure media.spxSystemEmailSend
    • Cycles through any unprocessed media.SystemEmail and sends these via msdb.dbo.sp_send_dbmail

The Synergetic default DB Mail profiles previously needed to be made public, this is now not required if security is wanted to be improved

NOTE: Attachments are sent, however at present they are not being stored in the table media.SystemEmailAttachment

Example Useage

former call

EXEC msdb..sp_send_dbmail 
    @profile_name = 'synergetic',
    @recipients = 'jblow@something.com.au',
    @subject = 'This is my subject',
    @body = 'This is my message body',
    @body_format = 'text'

now looks like

EXEC media.spiSystemEmail
@ProfileName = 'synergetic',
@Recipients = 'jblow@something.com.au',
@Subject = 'This is my subject',
@Body = 'This is my message body',
@BodyFormat = 'text'


The full parameter list available to media.spiSystemEmail closely resembles sp_send_dbmail and is as follows:-

 @ProfileName                 VARCHAR(128), -- We always want a profile name, even though sp_send_dbmail allows NULL
@Recipients VARCHAR(MAX) = NULL,
@CopyRecipients VARCHAR(MAX) = NULL,
@BlindCopyRecipients VARCHAR(MAX) = NULL,
@Subject VARCHAR(255) = NULL,
@BodyFormat VARCHAR(20) = 'TEXT',
@Importance VARCHAR(6) = 'NORMAL',
@Sensitivity VARCHAR(12) = 'NORMAL',
@FileAttachments VARCHAR(MAX) = NULL,
@ExecuteQueryDatabase VARCHAR(128) = NULL,
@AttachQueryResultAsFileFlag BIT = 0,
@QueryAttachmentFilename VARCHAR(260) = NULL,
@QueryResultHeaderFlag BIT = 1,
@QueryResultWidth INT = 256,
@QueryResultSeparator CHAR(1) = ' ',
@ExcludeQueryOutputFlag BIT = 0,
@AppendQueryErrorFlag BIT = 0,
@QueryNoTruncateFlag BIT = 0,
@QueryResultNoPaddingFlag BIT = 0,
@FromAddress VARCHAR(MAX) = NULL,
@SystemEmailSeq INT = NULL OUTPUT -- this is the identity value of the media.SystemEmail created as a result of a call to this procedure


Please note that these parameter names - with the exception of @SystemEmailSeq are an almost direct replacement for sp_send_dbmail parameters.  This means that help on the parameters can be easily obtained by searching online. eg https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql

DB Mail all sent items > 24 Hours
DB Mail - All sent items

SELECT p.name, i.send_request_date, i.sent_date, i.recipients, i.subject, i.body
FROM msdb..sysmail_mailitems AS inner join msdb..sysmail_profile as on p.profile_id = i.profile_id
WHERE sent_date > DATEADD(DAY, -1,GETDATE())
-- V68 SysEmail and dbMail:
-- 1. Synergetic writes to a Hold table (SystemEmail)
select from media.SystemEmail order by desc
-- 2. Every minute Sql agent job picks it up and sends it using dbmail
SELECT send_request_date, * FROM msdb.dbo.sysmail_allitems ORDER BY DESC

DB Mail all failed items > 24 hours
DB Mail - failed items

SELECT        items.subject, items.recipients, items.copy_recipients, items.blind_copy_recipients, items.last_mod_date, l.description
FROM            msdb.dbo.sysmail_faileditems AS items LEFT OUTER JOIN
                         msdb.dbo.sysmail_event_log AS ON items.mailitem_id = l.mailitem_id
WHERE        (items.last_mod_date > DATEADD(DAY, - 1, GETDATE()))