Azure Automation SQL Server stored procedures

There is no SQL Agent in Azure databases., but… we have azure automation. In the gallery you can browse and find a sample for executing a sql query. I have modified the sample to execute a stored procedure.

I am using it for machine learning… I will post about that later, here goes the code.



Outputs the number of records in the specified SQL Server database table.


This runbook demonstrates how to communicate with a SQL Server. Specifically, this runbook

outputs the number of records in the specified SQL Server database table.

In order for
this runbook to work, the SQL Server must be accessible from the runbook worker

running this runbook.Make sure the SQL Server allows incoming connections from Azure services

by selecting ‘Allow Windows Azure Services’ on the SQL Server configuration page in Azure.

This runbook also requires an Automation Credential asset be created before the runbook is

run, which stores the username and password of an account with access to the SQL Server.

That credential should be referenced for the SqlCredential parameter of this runbook.


String name of the SQL Server to connect to

.PARAMETER SqlServerPort

Integer port to connect to the SQL Server on


String name of the SQL Server database to connect to

.PARAMETER StoredProcedure

String name of the database table to output the number of records of

.PARAMETER SqlCredential

PSCredential containing a username and password with access to the SQL Server


Use-SqlCommandSample -SqlServer “” -SqlServerPort 1433 -Database “SomeDatabaseName” -Table “SomeTableName” – SqlCredential $SomeSqlCred


AUTHOR: System Center Automation Team

LASTEDIT: Jan 31, 2014


workflow SQLForAzure



[parameter(Mandatory =$True)]

[string] $SqlServer,

[parameter(Mandatory =$False)]

[int] $SqlServerPort = 1433,

[parameter(Mandatory =$True)]

[string] $Database,

[parameter(Mandatory =$True)]

[string] $StoredProcedure,

[parameter(Mandatory =$True)]

[PSCredential] $SqlCredential


Get the username and password from the SQL Credential

$SqlUsername = $SqlCredential.UserName

$SqlPass = $SqlCredential.GetNetworkCredential().Password



Define the connection to the SQL Database

$Conn = New-Object System.Data.SqlClient.SqlConnection(“Server=tcp:$using:SqlServer,$using:SqlServerPort;Database=$using:Database;User ID=$using:SqlUsername;Password=$using:SqlPass;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;”)

Open the SQL connection


Define the SQL command to run. In this case we are getting the number of rows in the table

$Cmd=newobject system.Data.SqlClient.SqlCommand(“$using:StoredProcedure”, $Conn)



Execute the SQL command

$Ds=New-Object system.Data.DataSet

$Da=New-Object system.Data.SqlClient.SqlDataAdapter($Cmd)


Output the count


Close the SQL connection




Leave a Reply

Your email address will not be published. Required fields are marked *