Post
Ask Question

Microsoft Flow With SQL Server

Introduction

 
In this blog, you will learn how we can send an email notification through Microsoft flow and SQL Server.
 
Purpose
 
We are going to create a Microsoft flow that will get the data using an SQL stored procedure and send the result via email.
 
The stored procedure (If you want to send the data in HTML format).
  1. DECLARE @CureentDate DATETIME  
  2.      DECLARE @tableHTML NVARCHAR(MAX);  
  3.      SET @CureentDate =GETDATE();  
  4.      SET @Month=FORMAT(@CureentDate, 'MMMM')  
  5.      SET @startHtml='<html><body>'  
  6.      SET @endHtml='</body></html>'  
  7.        
  8.   SELECT  (FirstName+' '+LastName) AS Name,BirthDate AS BirthDate  
  9.   INTO #Sheet1  
  10.   From Employee  WITH(NOLOCK)  
  11.   
  12. Set @BirthDaytable=N'<H3>Birth Date in '+@Month +'</H3>'+ '<table border="1" style="border:1px solid #77bfe4;font-family:Tahoma; font-weight:normal; font-size:12px;" cellpadding="4" cellspacing="0">' + '<tr bgcolor="#E0E0E0" style="font-family:Tahoma; font-weight:bold; font-size:12px;"><td><center>Name</center></td><td><center>BirthDate</center></td><td></td>  
  13.         </tr>' + CAST((  
  14.                     SELECT   
  15.                           
  16.                         td = K.Name  
  17.                         ,''  
  18.                         ,td =CONVERT(CHAR(11),K.BirthDate,113)   
  19.                         ,''  
  20.                     FROM #Sheet1 AS K  
  21.                     ORDER BY K.BirthDate DESC  
  22.                     FOR XML PATH('tr')  
  23.                         ,TYPE  
  24.                         --) AS NVARCHAR(MAX)) + '</table><br>Thank you</br></body></html>'  
  25.                     ) AS NVARCHAR(MAX)) + '</table>'  
  26.   
  27.   
  28.       SET @tableHTML=@startHtml+@BirthDaytable+@endHtml  
  29.   
  30.    Select @tableHTML  
Now we are going to create flow. When you log into the Microsoft flow, the below screen will show. Then click on Create.
 
So now we are going to use scheduled flow. It will give the below four options in the image.
 
 
 
When we click the scheduled flow below screen will come, you can see it gives multiple options for scheduling.
 
 
 
First, give the name of the scheduler flow name, then click on the New Step button.
 
 
 
Select the action that we need to perform to execute an SQL stored procedure. I am selecting Execute Stored procedure in the new step
 
Note: Premium connectors have some charges.
 
 
 
In 'execute a stored procedure', we can manage the SQL connection and add the new SQL connection using the rightmost three Dots
 
 
 
Then, select the connection string Name as whatever server name you saved when you added a connection. Select the DB name and Procedure Name that you have created.
 
One more step; send an email (action), and in the email body section, you can easily select the output of the procedure, as shown in the below image:
 
 
 
If you want to directly execute the SQL statement (script) then you can select Execute a SQL Query Action and set the output of the result on an Email body.
 
 
 
Now your flow is done. Save the flow and you can test the flow as shown in the below image: