It is important in a lot of systems to be able to run queries on a regular schedule. Previously I have outlined that this can be done using an automation account. It is also possible to accomplish this using an Azure Logic App to call a Stored Procedure.
The procedure is
Create Logic App
Add items to it
Firstly using SSMS connect to the database and create an example stored procedure.
CREATE PROCEDURE [dbo].[TestInsert] AS BEGIN SET NOCOUNT ON INSERT INTO dbo.T0003NLPRSitesTarget (sitename) SELECT Sitename from dbo.T0003NLPRSites END GO
Next log into your Azure Portal and identify the following icon
Select it and then hit the Add button..
You will be presented with the following screen
Now you should be taken to an overview showing that the logic app has been created
Hit Go to resource
You should be presented with the following screen
Here we choose Recurrence
You will be presented with the following Logic Apps Designer
Here is an example
Interval is like how many times the logic app will run over what Frequency
So if we put 3 in Interval and Frequency Day it would run once every 3 days
At these hours – sets specific times when the logic app will run within the set Frequency
On these days – allows for you to set what days the logic app will run At these hours
To explain the logic app running work from the bottom up. So the below example
On Monday, Tuesday , Wednesday, Thursday and Friday at 9,10,11,12,13,14,15,16 and 17 hours
Every week this will run (If Interval had been set to 3 it would have been every three weeks on Monday,Tuesday,Wednesday, Thursday and Friday at the hours 9,10,11,12,13,14,15,16 and 17 hours it would run.
Now hit next step and then type in execute stored procedure
You will first need to set up the connection to the database and then enter the parameters of the server database name database username and password or your chosen security to connect to the database and then you will be taken to the appropriate item.
You will need to set up the firewall on the server to ensure otherwise the logic app is likely to stop working at somepoint..
You can get a list of the appropriate firewall rules to set up by going here..
Managed connectors outbound IP addresses
So after further testing with the following parameters I discovered that in practice SQL Azure is a bit loose about the hours recurrence option. For example the following
Has been runnnig at 43minutes past the hour. Which is indicative of when I hit a run trigger at..
I will be checking to see when it next runs but I suspect it is going to be 17:43