Creating a schedule to run a SQL stored procedure from PowerShell script using Azure automation

Scenario: We have a SQL stored procedure, which we want to schedule to run once in a month. Our database is on Azure, and we don’t have an Azure VM or SQL server, we only have an Azure database. So, to run this procedure automatically, we can’t create any SQL job.
After some research, we decided to create a runbook and schedule the PowerShell script.
Below were the steps we followed –

Step 1. Create Automation Account –
Login to https://portal.azure.com
Go to All services then Automation Accounts

Click Add.

Fill all the details for the account and click Create.

Now, you can see that the newly created account is added to the list.

Step 2. Create a Runbook-
Open this newly created account and go to  Runbooks : Create a Runbook

Select PowerShell as Runbook type and create the Runbook.

It will take some time to create the Runbook.

Step 3. PowerShell Script–
When the Runbook got created paste below script in the Edit PowerShell Runbook

$sqlSvrName=“DBServerName”

$sqlDbName=“DatabaseName”

$sqlUserName=“DatabaseUserName”

$sqlPassword=“DatabaseUserPassword”

$sqlStoredProcedure=“StoredProcedureName”

$sqlConnection = New-Object System.Data.SqlClient.SqlConnection

$sqlConnection.ConnectionString = “Server=$sqlSvrName;uid=$sqlUserName;pwd=$sqlPassword;Initial Catalog=$sqlDbName“

$startDateTime = Get-Date

$sqlConnection.Open()

select $sqlConnection.ConnectionString

$sqlCmd = New-Object System.Data.SqlClient.SqlCommand

# specify that command is a stored procedure

$sqlCmd.CommandType=[System.Data.CommandType]'StoredProcedure'

# specify the name of the stored procedure

$sqlCmd.CommandText = $sqlStoredProcedure

$sqlCmd.Connection = $sqlConnection 

#————- specify optional parameters to stored procedure

#$startDateTime=$sqlCmd.Parameters.Add(“@rundatetime” , [System.Data.SqlDbType]::DateTime)

$startDateTime = Get-Date

 $sqlCmd.ExecuteNonQuery() 

Step 4. Test the PowerShell Script–
You can test the script by opening the test pane.

Click the start button to start testing the script.

After testing the script, publish it so you can schedule it.

Step 5. Schedule the Runbook
Go to Resources Go to Schedule, then Add a schedule.

Go to Schedule then Create a new schedule then Fill the schedule details as per the requirements.

After clicking OK, the newly created Schedule gets listed out in the schedule list, as shown below.

Congratulations, you have just created a Schedule using Azure Automation to run a SQL stored procedure from PowerShell Script.

Key Note: Other than PowerShell script, Azure Automation also supports several other types of Runbooks like Graphical (created and edited in graphical editor on Azure portal), Graphical PowerShell Workflow, PowerShell Workflow and Python.

If you are planning to create a Schedule to run a SQL stored procedure from PowerShell Script using Azure Automation or any other such combinations, get in touch with our team of experts or add your comments below in case of query.