Friday, October 28, 2022

Azure SQL Database - something like sp_send_dbmail

 I needed the ability to send emails from Azure SQL DB, which doesn't support it. The long-term goal is to use LogicApps or SendGrid or something like that, but while I wait for permissions and the like, let me share this instead. It acts like sp_send_dbmail, and will even run queries in the database (or other databases on that server!) and send them from your on-prem server.

This is based on others. They had the awesome parts, my parts are the crappy glue making it work. : )


The emailqueue table looks like this:

CREATE TABLE [dbo].[EmailQueue](
[Id] [INT] IDENTITY(1,1) NOT NULL,
[Recipients] [VARCHAR](250) NOT NULL,
[Cc_recipients] [VARCHAR](250) NULL,
[Email_Subject] [VARCHAR](250) NOT NULL,
[Email_body] [VARCHAR](MAX) NULL,
[Email_body_format] [VARCHAR](10) NULL,
[Query] [NVARCHAR](MAX) NULL,
[profile_name] [VARCHAR](250) NULL,
[QueueTime] [DATETIME2](7) NOT NULL,
[SentTime] [DATETIME2](7) NULL,
[Importance] [VARCHAR](10) NULL,
[attach_query_result_as_file] [BIT] NULL,
[query_attachment_filename] [VARCHAR](200) NULL,
[query_result_header] [BIT] NULL,
[query_result_width] [INT] NULL,
[query_result_separator] [VARCHAR](2) NULL,
[query_result_no_padding] [BIT] NULL,
[bcc_recipients] [VARCHAR](255) NULL,
[execute_query_database] [VARCHAR](255) NULL,
[from_address] [VARCHAR](MAX) NULL,
PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[EmailQueue] ADD  CONSTRAINT [Co_Importance]  DEFAULT ('Normal') FOR [Importance]
GO
ALTER TABLE [dbo].[EmailQueue] ADD  DEFAULT ((0)) FOR [attach_query_result_as_file]
GO
ALTER TABLE [dbo].[EmailQueue] ADD  DEFAULT ((1)) FOR [query_result_header]
GO
ALTER TABLE [dbo].[EmailQueue] ADD  DEFAULT ((0)) FOR [query_result_no_padding]
GO


And the SP: 

CREATE PROCEDURE [dbo].[log_dbmail] 

   @profile_name               sysname    = NULL,        

   @recipients                 VARCHAR(MAX)  = NULL, 

   @copy_recipients            VARCHAR(MAX)  = NULL,

   @blind_copy_recipients      VARCHAR(MAX)  = NULL,

   @subject                    NVARCHAR(255) = NULL,

   @body                       NVARCHAR(MAX) = NULL, 

   @body_format                VARCHAR(20)   = NULL, 

   @importance                 VARCHAR(6)    = 'NORMAL',

   @sensitivity                VARCHAR(12)   = 'NORMAL',

   @file_attachments           NVARCHAR(MAX) = NULL,  

   @query                      NVARCHAR(MAX) = NULL,

   @execute_query_database     sysname       = NULL,  

   @attach_query_result_as_file BIT          = 0,

   @query_attachment_filename  NVARCHAR(260) = NULL,  

   @query_result_header        BIT           = 1,

   @query_result_width         INT           = 256,            

   @query_result_separator     VARCHAR(2)       = ' ',

   @exclude_query_output       BIT           = 0,

   @append_query_error         BIT           = 0,

   @query_no_truncate          BIT           = 0,

   @query_result_no_padding    BIT           = 0,

   @mailitem_id               INT            = NULL OUTPUT,

   @from_address               VARCHAR(MAX)  = NULL,

   @reply_to                   VARCHAR(MAX)  = NULL

AS 

INSERT INTO dbo.EmailQueue

(

    Recipients,

    Cc_recipients,

Bcc_recipients,

    Email_Subject,

    Email_body,

    Email_body_format,

Query,

Execute_Query_Database,

attach_query_result_as_file,

query_attachment_filename,

query_result_header,

query_result_width,

query_result_separator,

query_result_no_padding,

    profile_name,

    QueueTime,

    SentTime,

Importance

)

VALUES

(   @recipients,            -- Recipients - varchar(250)

    @copy_recipients,          -- Cc_recipients - varchar(250)

@blind_copy_recipients,

    @subject,            -- Email_Subject - varchar(250)

    @body,          -- Email_body - varchar(max)

    @body_format,          -- Email_body_format - varchar(10)

@query,

@execute_query_database,

@attach_query_result_as_file,

@query_attachment_filename,

@query_result_header,

@query_result_width,

@query_result_separator,

@query_result_no_padding,

@profile_name,          -- profile_name - varchar(250)

    CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Central Standard Time' AS DATETIME), -- QueueTime - datetime2

    NULL,           -- SentTime - datetime2

@importance

    )



The powershell that pulls it all together: 

No comments: