How to Send Emails from Azure SQL Database with Azure Logic Apps

Problem

Azure SQL database is a fully managed relational database in the Microsoft cloud. It is a scalable, durable and performant database engine that allows developers to focus on the business logic without worrying about storage or server uptime. The main difference between SQL Server on premises and Azure SQL Database is that in Azure SQL Database only database level features are available. Features, like SQL Server Agent or SQL Server Mail are not supported by Azure SQL Database. However, in some situations we want to send an email from inside stored procedures. This post will show how to solve this challenge.

Solution

In order to be able to send emails from a stored procedure we can use additional Azure services, besides Azure SQL Database. In the below solution we will use Azure Logic Apps, which is a cloud integration platform. This platform helps us to build complex stateless workflows and has many built in connectors. We will create a table in SQL Database. For each new email, a SQL procedure will add a new row with all the email details. Logic Apps will check for new entries and use a SMTP server to send the email.

Create Emails Table in Azure SQL Database

For Azure, the table must have an identity column to work.

CREATE TABLE EmailQueue(
    Id              int             NOT NULL PRIMARY KEY IDENTITY(1,1),
    Recipients      varchar(250)    NOT NULL,
    Cc_recipients   varchar(250)    NULL,
    Email_Subject   varchar(250)    NOT NULL,
    Email_body      varchar(max)    NULL,
    QueueTime       datetime2       NOT NULL,
    SentTime        datetime2       NULL
);

Loading our first email. You can use multiple recipients or CC recipients separating the email addresses using a semicolon and have a rich HTML email body.

INSERT INTO EmailQueue (Recipients,Cc_recipients,Email_Subject,Email_body,QueueTime)
    VALUES ('maria@madeiradata.com;guy@gmail.com','someoneelse@madeiradata.com','Blogpost is ready',
            '<html><h1>Nice try</h1><p>Have a great day!</p></html>',getdate()
            )

Here is what we have now in our Email Queue table. One email is waiting to be sent, note the SentTime is empty currently. We will update that column after the email is sent.

The Logic App expects that the source table has an identity column and will assume that every new row's identity value has to be bigger than the previous one. Therefore, in case the identity sequence is reset for a table due to an explicit T-SQL command or table truncation, the trigger logic will skip new rows with the existing flow. There are two different workarounds possible. First, you can reset the flow trigger state by updating the trigger action card (rename it, update parameters, add new connection) and save the changes. Otherwise, you can restore the target table identity sequence. Note that turning off and turning on the flow will not reset the trigger state.

Set up Azure Logic Apps

We will create a Consumption Logic App.

Use a Blank template.

First, we need to choose Trigger – filter for SQL and chose “when an item is created”.

Choose Database and Server and how often you are going to poll the table.

You can either use an SMTP Action in the flow or Outlook 365. I will use Outlook 365.

Chose in parameters To, CC, Subject and Body and use Dynamic Content to fill the output received from the email queue table using the Trigger.

It will look this way.

Last Action that we will add, is “Execute a SQL Query” action that will update our table after the email is sent. There is a simple update query where row id is the Id we have received from the Trigger Dynamic parameters.

Save your Logic App and click Run the Trigger to see how it works.

Trigger History

In the Logic App Overview blade, we will see all trigger invocations. Skipped status means there were no new emails and “Succeeded” status means that new email was found.

In the Runs History you will see when all app flow was executed.

Here is what we have now in the email queue table, note that we have the timestamp when the email was sent.

Notification Email

And here is an email that I have received.

Summary

Important to know that multiple entries in the Emails table will trigger separate Logic Apps executions per row. Also, the first 4000 executions of Logic Apps are free of charge, but you should track your usage.

Logic Apps is a great orchestration service which can help to easily perform administrative tasks and is very configurable and user friendly. Logic Apps pricing is explained here.

Next Steps

Resources

Community

Subscribe