{"id":2756,"date":"2021-03-16T08:00:00","date_gmt":"2021-03-16T05:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=2756"},"modified":"2025-01-15T14:35:18","modified_gmt":"2025-01-15T11:35:18","slug":"pos-mporoyme-na-kanoyme-schedule-ena-job-se-azure-sql-database-me","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-mporoyme-na-kanoyme-schedule-ena-job-se-azure-sql-database-me\/","title":{"rendered":"How can we schedule a Job in Azure SQL Database using Logic App"},"content":{"rendered":"

Let's say that every day we would like to send by email a report from the result of a query in a database. This process in SQL Server<\/strong> is done by using it SQL Server Agent<\/em> and his Database Mail<\/em>. At Azure SQL Databases <\/strong>but there is neither.<\/p>\n\n\n\n

At Azure SQL Databases <\/strong> however, we can schedule jobs in many ways such as using Azure Data Factory<\/strong>, Elastic Database Jobs<\/strong> and Logic Apps<\/strong>. <\/p>\n\n\n\n

After Logic Apps<\/strong> we can create one workflow <\/strong>(workflow) being done trigger <\/strong>from an event or schedule.<\/p>\n\n\n\n

In the article we will see an example using Logic App<\/strong>. We will create a task that every day will save the results of a query in a CSV and send it with Gmail (via API).<\/p>\n\n\n\n

The example<\/h2>\n\n\n\n

To begin with, we connect to Azure Portal<\/a> and we are looking for the Logic apps service.<\/p>\n\n\n\n

\"\"
1<\/figcaption><\/figure>\n\n\n\n

We choose Add<\/em>.<\/p>\n\n\n\n

\"\"
2<\/figcaption><\/figure>\n\n\n\n

Then we select it Resource Group<\/em> which will belong, his name Logic App<\/em> and Region<\/em>.<\/p>\n\n\n\n

\"\"
3<\/figcaption><\/figure>\n\n\n\n

After it is created, we look for the option a little further down Blank Logic App<\/strong>.<\/p>\n\n\n\n

\"\"
4<\/figcaption><\/figure>\n\n\n\n

In the tab All<\/em> we find it Schedule<\/em>.<\/p>\n\n\n\n

\"\"
5<\/figcaption><\/figure>\n\n\n\n

In the case that we want if the task fails to try again after a while we will choose Sliding Window<\/strong>, if we want it to run the next time it is scheduled in case of failure, we choose Recurrence<\/strong>.<\/p>\n\n\n\n

Here now we will choose Recurrence<\/strong>.<\/p>\n\n\n\n

\"\"
6<\/figcaption><\/figure>\n\n\n\n

We choose Add new parameter<\/em> so we can add it Start Time <\/em>and Time zone<\/em>.<\/p>\n\n\n\n

\"\"
7<\/figcaption><\/figure>\n\n\n\n

In the next step we go to the tab again All <\/em>and we choose SQL Server<\/strong>.<\/p>\n\n\n\n

\"\"
8<\/figcaption><\/figure>\n\n\n\n

It will show us various things Actions<\/em>, we want it Execute stored procedure (V2).<\/strong><\/p>\n\n\n\n

\"\"
9<\/figcaption><\/figure>\n\n\n\n

Then it will ask us to enter the information to connect to the database.<\/p>\n\n\n\n

\"\"
10<\/figcaption><\/figure>\n\n\n\n

If we press it Create <\/em>and it shows us this message, it means that we should add it IP <\/strong>of Logic App at Firewall exceptions<\/strong> of Azure SQL Database.<\/p>\n\n\n\n

\"\"
11<\/figcaption><\/figure>\n\n\n\n

To do this, go to the logical Server of the base, Firewalls<\/em> and virtual networks<\/em>, Client IP address<\/em>.<\/p>\n\n\n\n

\"\"
12<\/figcaption><\/figure>\n\n\n\n

After it is added we will see that when we select it Create <\/em>will proceed.<\/p>\n\n\n\n

Then we select it Server name <\/em>and Database name<\/em> with what was created before.<\/p>\n\n\n\n

We choose it Add new parameter<\/em> so we add its parameter query<\/em>.<\/p>\n\n\n\n

In the query <\/strong>fill in the query that we want to run each time.<\/p>\n\n\n\n

\"\"
13<\/figcaption><\/figure>\n\n\n\n

In the next step we want the results to be stored in a CSV <\/strong>so at New step<\/em> we are looking in the tab All <\/em>the Data Operations <\/strong>and from there the Action <\/em>Create CSV table<\/strong>.<\/p>\n\n\n\n

There we press on from <\/em>and we choose it ResultSets<\/em>.<\/p>\n\n\n\n

\"\"
14<\/figcaption><\/figure>\n\n\n\n

At this point we will look into the New step<\/em> the Gmail<\/strong>, there we will select it Action <\/em>Send email (V2)<\/strong>.<\/p>\n\n\n\n

Gmail will then ask us with an API pop-up for the details of the email that will be the sender in order to give access to the Logic App.<\/p>\n\n\n\n

After we connect and it leads us to Send email (V2)<\/em> we should go to Add new parameter<\/em> and to add Attachments<\/strong>, Subject<\/strong> and Body<\/strong>. To add the attachment we should press it add dynamic content<\/em> in the Attachments Content<\/strong><\/em> , see more<\/em>, Output<\/em>.<\/p>\n\n\n\n

\"\"
15<\/figcaption><\/figure>\n\n\n\n

Now we can test the application by selecting Run<\/strong>.<\/p>\n\n\n\n

(don't forget to save it)<\/p>\n\n\n\n

\"\"
16<\/figcaption><\/figure>\n\n\n\n

we will immediately see an email containing the attachment from the database query.<\/p>\n\n\n\n

\"\"
17<\/figcaption><\/figure>\n\n\n\n

In the Overview <\/em>of the Logic App we can see general information such as when it will run and the history of its executions.<\/p>\n\n\n\n

\"\"
18<\/figcaption><\/figure>\n\n\n\n

Sources:<\/h2>\n\n\n\n