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: 

[Files] Tricking Excel with SP_SEND_DBMAIL

My pain = your gain. I needed to automate a process to send a file to an end user. They would just double-click it and have it open in Excel. The problem was due to a field with a leading zero, which Excel will simply lop off. One way around is to create an XML file. I've done that before, but this is simpler and ideal for this situation. The syntax and parameters are important.

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'youremailprofile', 
    @recipients = 'dev@null.com', 
    @subject = 'Here is your file pull done',
    @query = 'SELECT a, b, convert(varchar(12),c) as C, char(61) + char(34) + leadingzerossuck + char(34) as leadingzerossuck FROM mytemptable', 
    @attach_query_result_as_file = 1, 
    @query_attachment_filename = 'yourfilename.csv', 
    @query_result_separator = ' ', -- tab
    @exclude_query_output = 1, 
    @append_query_error = 1