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.

You have to add a $using:somParam to access parameters in the inlinescript. See https://technet.microsoft.com/en-us/library/jj574197(v=ws.11).aspx. — Mitch Stewart, May 11 '17 at 20:59

Mario Puglisi · Accepted Answer · 2016-03-01T06:53:12.760

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.

You should be able to run this script as PowerShell Workflow as you do above or as a simple PowerShell script with some minor code changes. Wrapping your code in inlinescript actually makes those cmdlets run as regular PowerShell. If you are using a PowerShell script runbook, remove the InlineScript and Workflow and it should just work. Running as regular PowerShell should actually be the preferred way because your scripts will start faster since they don't need to compile. — Elizabeth Cooper, Mar 01 '16 at 17:37
Thank you very much for your advice. I will try to use it as you say, I'm really noob with PowerShell. — Mario Puglisi, Mar 03 '16 at 07:31
@ElizabethCooper your line `If you are using a PowerShell script runbook, remove the InlineScript and Workflow and it should just work` explains a lot to me. Could not find it on the official docs website. Or have missed it while reading. I had workflow and inlinescript and could not get a write-ouput "hello world" to work. worked without workflow and inline script and locally. this explains why. Thank you! I have upvoted your comment. — JP Hellemons, Jul 22 '16 at 12:54
@MarioAlejandroPuglisiVieira Do you have to utilize a "Run As" account for this solution to work? — George D., Jan 26 '17 at 02:45

score 0 · Answer 2 · answered Feb 29 '16 at 01:52

0

The following article and subsequent sample code should be a good starting place to get PowerShell code executing against Azure SQL Database from Azure Automation: https://azure.microsoft.com/en-us/blog/azure-automation-your-sql-agent-in-the-cloud/

answered Feb 29 '16 at 01:52

Joseph Idziorek

4,853
6
23
37

Thank you very much for your rapid response, in fact I already saw that article. I´m trying to understand its code and modify it a bit to use it for what i needed but it didn't worked. – Mario Puglisi Feb 29 '16 at 03:56

How to run stored procedure using Azure Automation?

2 Answers2