Configuring email notifications in Azure SQL database
SQLShack
SQL Server training Español
Configuring email notifications in Azure SQL database
July 24, 2019 by Ranga Babu In this article, we will review how to configure email notifications in the Azure SQL single database or elastic pool. Azure SQL single database or elastic pool does not have database mail feature to send emails using sp_send_dbmail like in Azure SQL Server managed instance or on-premises SQL Server. We will walk through one of the alternatives to send emails in the Azure SQL database. The following are the steps involved in sending the emails from an Azure SQL database. Creating a SendGrid account Creating a Logic App Creating a trigger and Actions in Logic App Let’s go over these steps one by one.
Creating a SendGrid account
SendGrid is a cloud-based service which delivers emails in Azure. It offers 25000 free emails every month and they charge a minimal fee beyond that. To create a SendGrid account, Login to the Azure portal. Search for SendGrid as shown in the below image. Click on the Add button to create a new account. Enter the name of the account, password, select free tier if you are testing SendGrid or if your Azure SQL database email notifications do not cross 25000 per month. Enter the contact information and accept terms. Click on the Create button. Please refer to the below image. Once the account is created, click on the account you just created and then click on Manage which will navigate to the send grid dashboard. Verify your email by clicking on “Confirm Email Address” in the email you received. Now we must create an API key that will be used in Logic App to send out emails. In SendGrid dashboard, Under Settings, Click on API Keys and then click on Create API Key. Enter the name of the API key and select the access level. In this case, I used Full Access. Click on Create and View. Copy the key and save it to use later while configuring the Logic App.
Configuring a Logic App
To configure a Logic App, Navigate to the Azure portal. Search for the logic app as shown in the below image. Click on Logic Apps from the services list. Click on Add. Enter the name of the app, choose resource group and location. Click on Create.
Creating trigger and Actions in Logic App
Before creating trigger and actions, we need to create a table in the Azure SQL database that holds the message data. In this demo, I am creating a sample table. Following is the T-SQL script to create a sample table. Please note that the table must have an identity column that will be used by Logic App to identify new rows in the table. 1 Create TABLE EmailNotifications (MessageID int identity(1,1),MessageText nvarchar(max) ) Navigate to All Resources page and click on the Logic App you created above. Click on Logic app designer as shown in the below image. In the designer, Click on the Blank Logic App and search for SQL Server. Under triggers, select when an item is created. Select the server name and the Azure SQL database on which you created the table. Enter username, password and click on Create. Select the table you created from the drop-down list and specify the interval. Now click on New Step and search for SendGrid as shown in the below image. Select the Send Email action and enter the connection name and the API key you created earlier. Click on Create. Enter the relevant address information in ‘From’ and ‘To’ fields and also enter the Subject of the email. In the Email body, click on Add dynamic content and select the MessageText column from the table. Please refer to the below image. Finally, save the Logic App. Now the trigger runs every three minutes and sees if any new records are inserted in the Azure SQL database table. If it finds any, it will send an email with MessageText as email body. Now I will insert a row in the table “EmailNotifications” in HTML format. 12345678910111213141516171819202122232425262728 INSERT INTO EmailNotifications (MessageText) VALUES ('
Sample Table
| FirstName | Lastname |
| Denis | Steven |
| Dane | Ainsworth |
| Seema | Dam |
') The identity column must be auto-generated. Do not set IDENTITY_INSERT on and manually insert the identity column value, the logic app does not recognize such inserts and does not fire email. Please refer to the below image for the email notification which was fired from the above insert. So, whenever I want to send an email notification, I will generate the message and insert into the EmailNotifications table on the Azure SQL database. we can also have other columns to store address and dynamically send out an email to different people or groups.
Send result set of a stored procedure as an attachment
We can also send the result set of a T-SQL query or a stored procedure as a CSV attachment. In the designer search for Schedule. Select Recurrence, specify the interval and click on the New step as shown below. Search for SQL server and add Execute stored procedure action. Select the procedure from the drop-down list. Add a new step and select the data operations. Select Create a CSV table in the actions. In the From field, add ResultSets Table1 which is the result set of the above procedure. Add a new step and search for SendGrid. Select send email action and enter all the required fields. In the attachment field, choose the output of the CSV table and save the Logic App. Now the schedule is invoked every three minutes, executes the procedure, export the result set to CSV table and attach the CSV table as an attachment in the SendGrid email. Please refer to the below image for sample email with an attachment.
Conclusion
In this article, we explored how to configure email notification in the Azure SQL database using SendGrid and Logic App. In case you have any questions, please feel free to ask in the comment section below. Author Recent Posts Ranga BabuSQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies Latest posts by Ranga Babu (see all) Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases - October 24, 2019 Overview of the Collate SQL command - October 22, 2019 Recover a lost SA password - September 20, 2019
Related posts
Static Data Masking in SSMS 18 How to manage Power BI dataset refresh failures Getting started with Azure Function Apps Top SQL Server Books How to configure Database Mail in SQL Server 25,589 Views
Follow us
Popular
SQL Convert Date functions and formats SQL Variables: Basics and usage SQL PARTITION BY Clause overview Different ways to SQL delete duplicate rows from a SQL Table How to UPDATE from a SELECT statement in SQL Server SQL Server functions for converting a String to a Date SELECT INTO TEMP TABLE statement in SQL Server SQL WHILE loop with simple examples How to backup and restore MySQL databases using the mysqldump command CASE statement in SQL Overview of SQL RANK functions Understanding the SQL MERGE statement INSERT INTO SELECT statement overview and examples SQL multiple joins for beginners with examples Understanding the SQL Decimal data type DELETE CASCADE and UPDATE CASCADE in SQL Server foreign key SQL Not Equal Operator introduction and examples SQL CROSS JOIN with examples The Table Variable in SQL Server SQL Server table hints – WITH (NOLOCK) best practices
Trending
SQL Server Transaction Log Backup, Truncate and Shrink Operations Six different methods to copy tables between databases in SQL Server How to implement error handling in SQL Server Working with the SQL Server command line (sqlcmd) Methods to avoid the SQL divide by zero error Query optimization techniques in SQL Server: tips and tricks How to create and configure a linked server in SQL Server Management Studio SQL replace: How to replace ASCII special characters in SQL Server How to identify slow running queries in SQL Server SQL varchar data type deep dive How to implement array-like functionality in SQL Server All about locking in SQL Server SQL Server stored procedures for beginners Database table partitioning in SQL Server How to drop temp tables in SQL Server How to determine free space and file size for SQL Server databases Using PowerShell to split a string into an array KILL SPID command in SQL Server How to install SQL Server Express edition SQL Union overview, usage and examples
Solutions
Read a SQL Server transaction logSQL Server database auditing techniquesHow to recover SQL Server data from accidental UPDATE and DELETE operationsHow to quickly search for SQL database data and objectsSynchronize SQL Server databases in different remote sourcesRecover SQL data from a dropped table without backupsHow to restore specific table(s) from a SQL Server database backupRecover deleted SQL data from transaction logsHow to recover SQL Server data from accidental updates without backupsAutomatically compare and synchronize SQL Server dataOpen LDF file and view LDF file contentQuickly convert SQL code to language-specific client codeHow to recover a single table from a SQL Server database backupRecover data lost due to a TRUNCATE operation without backupsHow to recover SQL Server data from accidental DELETE, TRUNCATE and DROP operationsReverting your SQL Server database back to a specific point in timeHow to create SSIS package documentationMigrate a SQL Server database to a newer version of SQL ServerHow to restore a SQL Server database backup to an older version of SQL Server
Categories and tips
►Auditing and compliance (50) Auditing (40) Data classification (1) Data masking (9) Azure (295) Azure Data Studio (46) Backup and restore (108) ►Business Intelligence (482) Analysis Services (SSAS) (47) Biml (10) Data Mining (14) Data Quality Services (4) Data Tools (SSDT) (13) Data Warehouse (16) Excel (20) General (39) Integration Services (SSIS) (125) Master Data Services (6) OLAP cube (15) PowerBI (95) Reporting Services (SSRS) (67) Data science (21) ►Database design (233) Clustering (16) Common Table Expressions (CTE) (11) Concurrency (1) Constraints (8) Data types (11) FILESTREAM (22) General database design (104) Partitioning (13) Relationships and dependencies (12) Temporal tables (12) Views (16) ►Database development (418) Comparison (4) Continuous delivery (CD) (5) Continuous integration (CI) (11) Development (146) Functions (106) Hyper-V (1) Search (10) Source Control (15) SQL unit testing (23) Stored procedures (34) String Concatenation (2) Synonyms (1) Team Explorer (2) Testing (35) Visual Studio (14) DBAtools (35) DevOps (23) DevSecOps (2) Documentation (22) ETL (76) ►Features (213) Adaptive query processing (11) Bulk insert (16) Database mail (10) DBCC (7) Experimentation Assistant (DEA) (3) High Availability (36) Query store (10) Replication (40) Transaction log (59) Transparent Data Encryption (TDE) (21) Importing, exporting (51) Installation, setup and configuration (121) Jobs (42) ►Languages and coding (686) Cursors (9) DDL (9) DML (6) JSON (17) PowerShell (77) Python (37) R (16) SQL commands (196) SQLCMD (7) String functions (21) T-SQL (275) XML (15) Lists (12) Machine learning (37) Maintenance (99) Migration (50) Miscellaneous (1) ►Performance tuning (869) Alerting (8) Always On Availability Groups (82) Buffer Pool Extension (BPE) (9) Columnstore index (9) Deadlocks (16) Execution plans (125) In-Memory OLTP (22) Indexes (79) Latches (5) Locking (10) Monitoring (100) Performance (196) Performance counters (28) Performance Testing (9) Query analysis (121) Reports (20) SSAS monitoring (3) SSIS monitoring (10) SSRS monitoring (4) Wait types (11) ►Professional development (68) Professional development (27) Project management (9) SQL interview questions (32) Recovery (33) Security (84) Server management (24) SQL Azure (271) SQL Server Management Studio (SSMS) (90) SQL Server on Linux (21) ►SQL Server versions (177) SQL Server 2012 (6) SQL Server 2016 (63) SQL Server 2017 (49) SQL Server 2019 (57) SQL Server 2022 (2) ►Technologies (334) AWS (45) AWS RDS (56) Azure Cosmos DB (28) Containers (12) Docker (9) Graph database (13) Kerberos (2) Kubernetes (1) Linux (44) LocalDB (2) MySQL (49) Oracle (10) PolyBase (10) PostgreSQL (36) SharePoint (4) Ubuntu (13) Uncategorized (4) Utilities (21) Helpers and best practices BI performance counters SQL code smells rules SQL Server wait types © 2022 Quest Software Inc. ALL RIGHTS RESERVED. GDPR Terms of Use Privacy