Working on-premises SQL having many advantages like you have the full administrative rights over a dedicated SQL Server instance, we can run scheduled Jobs using SQL Agent Job. Speaking of SQL Agent Job, I personally a huge fan of this. We can do a lot and lots of stuff like schedule jobs, view the job history, Run manual jobs, Sending Emails etc.,
But when coming into DBaaS (Database as a Service) which is nothing but Azure SQL Database. There are no such options like Agent Jobs. Since it’s a Paas like service, we just own our code and not the Infrastructure and Services (like Agent Jobs).
Most of them have a requirement to send daily reports to our some concerned person/admin via Email with the information about some details which are updated/created on that day (Let say for an instance it is user details ). If we have a traditional on-premises SQL then it will a simple task by creating a Stored Procedure, then run the SP using Agent Job for a particular time period. But for the cases like Azure SQL Database, we need to find other approaches.
In this blog, we will see gonna see how this can be achieved easily and efficiently using Logic Apps.
- Azure Subscription– We need to have a valid Azure Subscription in order to create any Azure resources like Logic Apps, Azure SQL Database. You can create a free Azure Account if you don’t have any.
- Azure Logic App – Once we have a valid Azure Subscription, we can create a logic App using Azure Portal / Visual Studio / Visual Studio Code
- Azure SQL Database – We need to have an Azure SQL Database, where our StoreProcedure will reside.
Our End goal is to send daily Reports via E-mail by gathering data(Executing SP) from an Azure SQL Database efficiently. To achieve our goal, our design should be more easy to manage, less cost computation, managing alerts/logs for troubleshooting purpose.
- Recurrence Task
- Execute Store Procedure Task
- Create CSV table Task
- Send an Email Task
The below image is the full design view of our final Logic App. We will see the detailed task one-by-one in the below topics.This view gives an overview idea of how the Logic App looks like.
This is where we actually set the Schedule to run our logic apps at the beginning of execution. I’ve scheduled it to run daily once at 1:00 AM (IST)
This is the task where we will connect to our Azure SQL Database and call the Stored Procedure we need.
In this task, we will dump our result set from the SP into a CSV table, which we will attach into our report mail in the next task
This will be the final stage of our Logic App, we will configure the Body, Subject, To-Email– Admin Email and the attachment (CSV file the from the previous task)
For the Attachment Name, I just used the concat operations
Attachment Name – 1
After creating all the task and connecting together as shown in the below figure, we are ready to test our Logic App. After clicking the
Run button our Logic App will start running the Task one after another. We can see the time taken to complete each task in the below figure. (Which is almost less than a Second)
- If the task is successfully executed it will be marked as a Green Tick at the top right Corner along with the time taken to complete that task
- In case of any error will be marked as a Red Exclamatory at the top right Corner along with the time taken to complete that task
- Logic Apps is inbuilt with the Retry logic, so in case of any error instead of changing into error status directly, it will retry up to 3/4 times.
It’s time to verify the mailbox. As expected I got the below Email, The Subject, the content of Body, the attachment came correctly as per we configured in the send an Email Task
Finally, we got our Reports in the CSV format, let try to verify that the data are present in the file. The file having the below contents which are the expected result.
Hurray! We finally achieved our goal easily and efficiently.
Using Logic Apps(Serverless App) we can easily send daily reports by executing Store procedure from Azure SQL Database. This blog will give you a simple flow design to implement it, we can also design with a complex workflow depends on the nature of the reports.