Automated stored procedures on Microsoft Azure

I needed to run a stored procedure in a Azure database every day at midnight.

To do this, set up an Azure automation account. Then create a new runbook with the following powershell script. Finally, you will need to create a credential asset to plug into this script as an input parameter.

Once it’s set up you can schedule it as a nightly job.

workflow DailyExpirationCheck 
        # Fully-qualified name of the Azure DB server 
        [string] $SqlServerName,
        # Database name on the Azure DB server 
        [string] $DatabaseName,
		# Credentials for $SqlServerName stored as an Azure Automation credential asset
		# When using in the Azure Automation UI, please enter the name of the credential asset for the "Credential" parameter
        [PSCredential] $Credential
        # Set up credentials   
        $ServerName = $Using:SqlServerName
        $DatabaseName = $Using:DatabaseName
        $UserId = $Using:Credential.UserName
        $Password = ($Using:Credential).GetNetworkCredential().Password
        # Create connection to DB
        $DatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
        $DatabaseConnection.ConnectionString = "Server = $ServerName; Database = $DatabaseName; User ID = $UserId; Password = $Password;"
        # Create command to execute stored procedures
        $DatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
        $DatabaseCommand.Connection = $DatabaseConnection
        $DatabaseCommand.CommandType = [System.Data.CommandType]::StoredProcedure
        # Update statuses by calling our stored procedure
        $DatabaseCommand.CommandText = 
        $DatabaseCommand.ExecuteNonQuery() | out-null
        if($? -eq 1)
            Write-Output "EndDate check successfully completed."   
        # Close connection to DB
        Write-Output "[Daily expiration checks complete.]"