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
Go to All services then Automation Accounts

Azure Automation 1

Click Add.

Fill all the details for the account and click Create.

Azure Active Directory 3

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

Azure Automation 4

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

Azure Automation 5

Select PowerShell as Runbook type and create the Runbook.

It will take some time to create the Runbook.


Azure Automation 6

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







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

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

$startDateTime = Get-Date



select $sqlConnection.ConnectionString


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

# specify that command is a stored procedure


# 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


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

Azure Automation 7

Click the start button to start testing the script.

Azure Automation 8

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

Azure Automation 8

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

Azure Automation 8

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

Azure Automation 8

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

Azure Automation 12

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.

shruti vyas

Shruti Vyas

Shruti is a technical consultant at Advaiya. With over nine years of experience in the IT industry, Shruti has a passion for data and an ability to understand and analyze it effectively. She shares her insight on various topics such as custom application development, project server customization, client-side scripting etc.

Posted by Advaiya

    Let's connect to make technology work for you.

    Please tick the options most relevant to your business challenges
    Decision makingBusiness productivityCustomer experienceTechnology-led innovationDigital transformation

    [By using this form you agree with the storage and handling of your data by this website.]