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,
 @Body                        VARCHAR(MAX) = NULL,
 @BodyFormat                  VARCHAR(20) = 'TEXT',
 @Importance                  VARCHAR(6) = 'NORMAL',
 @Sensitivity                 VARCHAR(12) = 'NORMAL',
 @FileAttachments             VARCHAR(MAX) = NULL,
 @Query                       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,
 @ReplyTo                     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 i inner join msdb..sysmail_profile as p 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 1 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 1 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 l ON items.mailitem_id = l.mailitem_id
WHERE        (items.last_mod_date > DATEADD(DAY, - 1, GETDATE()))