How to run stored procedure using Azure Automation?

Question

I´m really new to the Azure platform and maybe this is a dumb question but i haven´t manage to find information about this topic. I really need help with this. I'm deploying a database used by a web service for a university project. In this database I have an stored procedure and need to run it daily.

Found that with Azure Automation you could program or schedule this kind of actions. I "installed" the service and I´m trying to create the "runbook" but don´t know how or what to code in here because I never used PowerShell that much.

Any help provided will be appreciated. Thanks in advance!

EDIT 1:

So I'm trying to use this code to make the magic:

 workflow WORKFLOW_NAME
{ 
    param( 

    ) 


    inlinescript { 
        # Define the connection to the SQL Database 
        $Conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=SERVER_NAME.database.windows.net;Initial Catalog=DATABASE_NAME;Integrated Security=False;User ID=USERNAME;Password=PASSWORD;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False") 

        # Open the SQL connection 
        $Conn.Open() 

        # Define the SQL command to run. 
        $Cmd=new-object system.Data.SqlClient.SqlCommand("exec PROCEDURE_NAME", $Conn) 
        $Cmd.CommandTimeout=120 

        # Execute the SQL command 
        $Da=New-Object system.Data.SqlClient.SqlDataAdapter($Cmd) 

        # Close the SQL connection 
        $Conn.Close() 
    } 
}

The thing is that when I save the RunBook and publish it, it says there is no errors. When I run the RunBook it starts and finish sending no errors nor exceptions, so you could expect it did the work right?, but when consulting the database there is no modifications in the tables the procedure is intended to modify. What could be the error in this? what am I doing wrong?

I took the reference code from https://gallery.technet.microsoft.com/scriptcenter/How-to-use-a-SQL-Command-be77f9d2#content , personalized it and got rid of the "param" because the RunBook when started, never asked for any entry parameters, so I decided to go with the full connection string. I'm using the same connection string as my c# project, which connects and works perfectly.

I'm using the "new" azure interface, don't know if this could be of any importance in this regard.

Again, thank you for any help you could provide.

Answer 1

I found the core of the problem, the code works just fine, the issue was I was using the wrong type of RunBook inside Azure Automation, so, make sure you're running a Workflow PowerShell instead of a simple PowerShell.

The code I posted in the question works, but I found a better way to understand what the code made by using the example provided here: https://azure.microsoft.com/en-us/blog/azure-automation-your-sql-agent-in-the-cloud/ (thanks to @Joseph Idziorek)

Here is the working code for anyone who ran into the same problem as i did:

workflow NAME-OF-YOUR-WORKFLOW
{
    Write-Output "JOB START BEFORE INLINESCRIPT"

    inlinescript
    {
        Write-Output "JOB START"
        # Create connection to Master DB
        $MasterDatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
        $MasterDatabaseConnection.ConnectionString = "Data Source=YOUR-DATABASE-SERVER-NAME.database.windows.net;Initial Catalog=YOUR-DATABASE-NAME;Integrated Security=False;User ID=YOUR-DATABASE-USERNAME;Password=YOUR-DATABASE-PASSWORD;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False"
        $MasterDatabaseConnection.Open()

        Write-Output "CONNECTION OPEN"

        # Create command
        $MasterDatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
        $MasterDatabaseCommand.Connection = $MasterDatabaseConnection
        $MasterDatabaseCommand.CommandText = "YOUR-PROCEDURE-NAME"

        Write-Output "DATABASE COMMAND TEXT ASSIGNED"

        # Execute the query
        $MasterDatabaseCommand.ExecuteNonQuery()

        Write-Output "EXECUTING QUERY"

        # Close connection to Master DB
        $MasterDatabaseConnection.Close() 

        Write-Output "CONNECTION CLOSED"
    }    
    Write-Output "WORK END - AFTER INLINESCRIPT"
}

The Write-outputs are optional, if you want to check what part of the code is working and if everything worked after each run.