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:
[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:
