Using Azure Logic App and an Automation Runbook to execute a long running SQL Stored Procedure

When trying to execute a long running SQL query using the Logic App SQL Connector, you will most likely hit the 2 minute execution timeout limit on the connector.

Even if you move the SQL query to an Azure function and use ADO.Net to access the backend database,  you will hit the 10 minute execution timeout limit of the function when using the consumption plan.

An Alternative Approach

The solution here is to use the Azure Automation service to execute a PowerShell script which calls a SQL query. There is now a new connector available in Logic Apps to execute an Automation Runbook which you have the option to wait until it completes before moving onto the next action shape in the workflow. 

Implementation

First find and add an Automation Account to your Azure resource group. Set the Create Azure Run As account to No.

image

Once the Automation Account has been created, click on the Credentials on the left menu pane to store the SQL Server login credentials.

image

Then add the SQL username and password and click Create. We will be using this as the Credential parameter in our PowerShell script.

image

Now click on Runbooks to create a new runbook. This is where we add a PowerShell script to execute.

image

Add a name for the Runbook and ensure the Runbook type is set to PowerShell Workflow, then click the Create button at the bottom of the page.

image

After the Runbook has been created, click on the Edit icon to add the PowerShell script.

image

Inside the PowerShell editor,  paste the following code. Basically we are using  ADO.Net object to create a SQL connection and to execute the stored procedure. To ensure the command object does not timeout, set the CommandTimeout = 0.

workflow ExecuteSQLStoredProcedure

{

    param(

        [parameter(Mandatory=$True)]

        [string] $SqlServer,

        

        [parameter(Mandatory=$False)]

        [int] $SqlServerPort=1433,

        

        [parameter(Mandatory=$True)]

        [string] $Database,

        

        [parameter(Mandatory=$True)]

        [string] $Sproc,

        

        [parameter(Mandatory=$True)]

        [PSCredential] $SqlCredential

    )

 

    # Get the username and password from the SQL Credential

    $SqlUsername = $SqlCredential.UserName

    $SqlPass = $SqlCredential.GetNetworkCredential().Password

    

    inlinescript {

        # Define the connection to the SQL Database

        $SQLConn = 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

        $SQLConn.Open()

 

        # Define the SQL command to run. 

        $Cmd =  New-Object System.Data.SQLClient.SqlCommand 

        $Cmd.Connection = $SQLConn 

        $Cmd.CommandTimeout=0

        $Cmd.CommandType = [System.Data.CommandType]::StoredProcedure

        $Cmd.CommandText = $using:Sproc

 

        $Cmd.ExecuteNonQuery()

 

        # Close the SQL connection

        $SQLConn.Close()

    }

}

After the code has been pasted into the editor, save and remember to  publish it after it has been saved.

 image

Now to add a Logic App to execute the Runbook. For this demo, I will simply use the scheduler connector to fire the Logic App. In the designer add a Recurrence trigger and for the next action, search for automation and select the Create Job Azure Automation.

image

The parameters defined in the PowerShell script will be shown in the properties page of the connector as shown below. I will be executing a stored procedure called “dbo.LongProcess” which has a WaitFor Delay ‘00:15’ T-SQL statement. This will simulate a long running query of 15 minutes.

image

To get the results of the SQL query add the Azure Automation Get job output shape after the Create job shape.

image

Testing

To confirm the Logic App and SQL query does not timeout, run the trigger manually. After a period of 15 minutes, which is the delay period defined in the stored procedure, the Logic App will complete as shown below.

image

The output of the stored procedure will be shown in the Get job output shape.

To return a recordset as JSON from the PowerShell script, replace the code $Cmd.ExecuteNonQuery() with the following:

$reader = $Cmd.ExecuteReader()

$table = new-object "System.Data.DataTable"

$table.Load($reader)

 

#--Exporting data to the screen --# 

$table | select $table.Columns.ColumnName | ConvertTo-Json

 
Note if you return a very large recordset, you may exceed the allocated memory,. In this scenario, you will need to batch the results.

Enjoy…

Using User Defined Table parameters of the same name in a stored procedure

When using a stored procedure that uses UDT (User defined table) you may get the following error  “An item with the same key has already been added” when using the Consume Adaptor Service as shown below.

image

This error was due because I was using the same UDT ([NameTVP]) for 2 of parameters in the stored procedure as shown below at lines 3 and 4.

   1: CREATE PROCEDURE dbo.bts_LoadAddress

   2:     -- Add the parameters for the stored procedure here

   3:     @Region [NameTVP] readonly,

   4:     @Town [NameTVP] readonly

 

It appears the adaptor wizard cannot handle several User Defined Tables using the same name  for the parameters. The simple resolution is to use distinct names for the UDT’s to avoid this error.