Send Emails from Azure SQL Database by using Azure Logic Apps

By:   |   Updated: 2021-11-22   |   Comments (1)   |   Related: > Azure


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.

select from table query results

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

  1. We will create a Consumption Logic App.
Create Logic App -. Consumption
  1. Use a Blank template.
Use Blank Template
  1. First, we need to choose Trigger – filter for SQL and chose "when an item is created".
Add a trigger
  1. Choose Database and Server and how often you are going to poll the table.
Chose connection details
  1. You can either use an SMTP Action in the flow or Outlook 365. I will use Outlook 365.
Chose action
  1. Sign in into your Microsoft account.
add an action
  1. 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.
send an email
  1. It will look this way.
send an email
  1. 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.
add an action
execute a sql query
  1. 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.

trigger history

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

runs history

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

query results

Notification Email

And here is an email that I have received.

sample email

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



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Maria Zakourdaev Maria Zakourdaev has been working with SQL Server for more than 20 years. She is also managing other database technologies such as MySQL, PostgreSQL, Redis, RedShift, CouchBase and ElasticSearch.

View all my tips


Article Last Updated: 2021-11-22

Comments For This Article




Friday, February 25, 2022 - 2:23:12 AM - Jason Nott Back To Top (89831)
That is not what currently comes up in Azure, or you skipped steps


download














get free sql tips
agree to terms