Execute a long running SQL Stored Procedure in Azure logic app using an Automation Account

Published Jan 20 2022 10:46 PM 2,028 Views

When trying to execute a long running SQL query/stored procedure using the Logic App SQL Connector, you will most likely hit the 110 seconds execution timeout limit on SQL connector: SQL Server - Connectors | Microsoft Docs

 

To overcome timeout limit issue, we do have multiple workarounds via Azure services and among them one of the solution is to use the Azure Automation service to execute a PowerShell script which calls a SQL query/stored procedure.

 

In this blog, we will see how to execute a long running SQL stored procedure via logic app with the help of automation account.

 

Automation Account Implementation:

 

1. First create/add Automation Account; Create a standalone Azure Automation account | Microsoft Docs

 

Saroja_L_Sattigeri_9-1642744754089.png

 

2. After Automation Account creation, under Automation Account, click on the 'Credentials' on the left menu pane to store SQL Server login credentials(SQL username, password which will be using this as the Credential parameter in PowerShell script)

 

Saroja_L_Sattigeri_10-1642744754077.png

 

 

 

3. Then click on Runbooks to create a new runbook under created automation account; This is where we add PowerShell script to execute-> Add a name for the Runbook and ensure the Runbook type is set to PowerShell Workflow, then click on create button.

Saroja_L_Sattigeri_11-1642744754079.png

 

4. After Runbook creation, click on the Edit icon to add PowerShell script

 

Saroja_L_Sattigeri_12-1642744754091.png

 

 

5. Inside the PowerShell editor,  paste the following code. Basically here we are using  ADO.Net object to create a SQL connection and to execute the stored procedure and 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()

    }

}

 

6. Then save the code and make sure to publish it after saved.

 

Saroja_L_Sattigeri_13-1642744754092.png

 

7. To add a Logic App to execute the Runbook:

 

  • After trigger-> search for 'automation' -> select the 'Create Job' action of Azure Automation connector-> Create API connection-> provide subscription, resource group and automation account->select 'Yes' for Wait for Job parameter so that it will wait for the job to finish before completing the action.

 

  • Then click on 'add new parameter' select 'Runbook' -> select your Runbook which you have created for executing SQL stored procedure-> once you select Runbook, parameters defined in the PowerShell script will be shown as below-> Runbook Parameter SQL server port will be using default TCP port 1433 of SQL server(which we have specified in PowerShell script),  provide values for 'Runbook parameter Database', 'Runbook Parameter SQL server', 'Runbook Parameter SQL credentials', 'Runbook Parameter sproc(stored procedure name):

 

Saroja_L_Sattigeri_14-1642744754082.png

 

 

 

  • After 'Create job' action, to get the results of the SQL stored procedure, add 'Get job output' action of Azure automation connector-> specify Subscription, Resource Group, Automation account(same automation account in which you are creating a job) and use 'Job ID' dynamic content of 'Create job' action.

 

Saroja_L_Sattigeri_15-1642744754083.png

 

 

 

 

Testing:

 

To confirm the Logic App and SQL stored procedure does not timeout, run the logic app and the output of the stored procedure will be shown in the Get job output action

 

Saroja_L_Sattigeri_16-1642744754084.png

 

 

Saroja_L_Sattigeri_17-1642744754085.png

 

 

Note: To return a record set 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

 

 If you return a very large record set, you may exceed the allocated memory so, in this scenario, batching the results will help.

 

References:

  1. Handle long-running stored procedures in the SQL connector - Azure Logic Apps | Microsoft Docs
  1. Azure Automation Connector document: Azure Automation - Connectors | Microsoft Docs
%3CLINGO-SUB%20id%3D%22lingo-sub-3068008%22%20slang%3D%22en-US%22%3EExecute%20a%20long%20running%20SQL%20Stored%20Procedure%20in%20Azure%20logic%20app%20using%20an%20Automation%20Account%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3068008%22%20slang%3D%22en-US%22%3E%3CP%3EWhen%20trying%20to%20execute%20a%20long%20running%20SQL%20query%2Fstored%20procedure%20using%20the%20Logic%20App%20SQL%20Connector%2C%20you%20will%20most%20likely%20hit%20the%20110%20seconds%20execution%20timeout%20limit%20on%20SQL%20connector%3A%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fconnectors%2Fsql%2F%23known-issues-and-limitations%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ESQL%20Server%20-%20Connectors%20%7C%20Microsoft%20Docs%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20overcome%20timeout%20limit%20issue%2C%20we%20do%20have%20multiple%20workarounds%20via%20Azure%20services%20and%20among%20them%20one%20of%20the%20solution%20is%20to%20use%20the%20Azure%20Automation%20service%20to%20execute%20a%20PowerShell%20script%20which%20calls%20a%20SQL%20query%2Fstored%20procedure.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20blog%2C%20we%20will%20see%20how%20to%20execute%20a%20long%20running%20SQL%20stored%20procedure%20via%20logic%20app%20with%20the%20help%20of%20automation%20account.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EAutomation%20Account%20Implementation%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1.%20First%20create%2Fadd%26nbsp%3B%3CSTRONG%3EAutomation%20Account%3C%2FSTRONG%3E%3B%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fautomation%2Fautomation-create-standalone-account%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ECreate%20a%20standalone%20Azure%20Automation%20account%20%7C%20Microsoft%20Docs%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Saroja_L_Sattigeri_9-1642744754089.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F341329iAC3ED1F54AC54A7A%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Saroja_L_Sattigeri_9-1642744754089.png%22%20alt%3D%22Saroja_L_Sattigeri_9-1642744754089.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E2.%20After%20Automation%20Account%20creation%2C%20under%20Automation%20Account%2C%20click%20on%20the%26nbsp%3B'%3CSTRONG%3ECredentials'%3C%2FSTRONG%3E%26nbsp%3Bon%20the%20left%20menu%20pane%20to%20store%20SQL%20Server%20login%20credentials(SQL%20username%2C%20password%20which%20will%20be%20using%20this%20as%20the%20Credential%20parameter%20in%20PowerShell%20script)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Saroja_L_Sattigeri_10-1642744754077.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F341330iEE9ED22B542D4BCB%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Saroja_L_Sattigeri_10-1642744754077.png%22%20alt%3D%22Saroja_L_Sattigeri_10-1642744754077.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E3.%20Then%20click%20on%26nbsp%3B%3CSTRONG%3ERunbooks%3C%2FSTRONG%3E%26nbsp%3Bto%20create%20a%20new%20runbook%20under%20created%20automation%20account%3B%20This%20is%20where%20we%20add%20PowerShell%20script%20to%20execute-%26gt%3B%20Add%20a%20name%20for%20the%20Runbook%20and%20ensure%20the%26nbsp%3B%3CSTRONG%3ERunbook%20type%3C%2FSTRONG%3E%26nbsp%3Bis%20set%20to%26nbsp%3B%3CSTRONG%3EPowerShell%20Workflow%2C%3C%2FSTRONG%3E%26nbsp%3Bthen%20click%20on%20create%20button.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Saroja_L_Sattigeri_11-1642744754079.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F341331i2BD1EBF86B6AC853%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Saroja_L_Sattigeri_11-1642744754079.png%22%20alt%3D%22Saroja_L_Sattigeri_11-1642744754079.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E4.%20After%20Runbook%20creation%2C%20click%20on%20the%20Edit%20icon%20to%20add%20PowerShell%20script%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Saroja_L_Sattigeri_12-1642744754091.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F341332iA602E3583595A323%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Saroja_L_Sattigeri_12-1642744754091.png%22%20alt%3D%22Saroja_L_Sattigeri_12-1642744754091.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E5.%20Inside%20the%20PowerShell%20editor%2C%26nbsp%3B%20paste%20the%20following%20code.%20Basically%20here%20we%20are%20using%26nbsp%3B%20ADO.Net%20object%20to%20create%20a%20SQL%20connection%20and%20to%20execute%20the%20stored%20procedure%20and%20to%20ensure%20the%20command%20object%20does%20not%20timeout%2C%20set%20the%20CommandTimeout%20%3D%200.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eworkflow%20ExecuteSQLStoredProcedure%3C%2FP%3E%0A%3CP%3E%7B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20param(%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5Bparameter(Mandatory%3D%24True)%5D%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5Bstring%5D%20%24SqlServer%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5Bparameter(Mandatory%3D%24False)%5D%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5Bint%5D%20%24SqlServerPort%3D1433%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5Bparameter(Mandatory%3D%24True)%5D%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5Bstring%5D%20%24Database%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5Bparameter(Mandatory%3D%24True)%5D%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5Bstring%5D%20%24Sproc%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5Bparameter(Mandatory%3D%24True)%5D%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5BPSCredential%5D%20%24SqlCredential%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%23%20Get%20the%20username%20and%20password%20from%20the%20SQL%20Credential%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%24SqlUsername%20%3D%20%24SqlCredential.UserName%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%24SqlPass%20%3D%20%24SqlCredential.GetNetworkCredential().Password%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20inlinescript%20%7B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%23%20Define%20the%20connection%20to%20the%20SQL%20Database%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%24SQLConn%20%3D%20New-Object%20System.Data.SqlClient.SqlConnection(%22Server%3Dtcp%3A%24using%3ASqlServer%2C%24using%3ASqlServerPort%3BDatabase%3D%24using%3ADatabase%3BUser%20ID%3D%24using%3ASqlUsername%3BPassword%3D%24using%3ASqlPass%3BTrusted_Connection%3DFalse%3BEncrypt%3DTrue%3BConnection%20Timeout%3D30%3B%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%23%20Open%20the%20SQL%20connection%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%24SQLConn.Open()%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%23%20Define%20the%20SQL%20command%20to%20run.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%24Cmd%20%3D%26nbsp%3B%20New-Object%20System.Data.SQLClient.SqlCommand%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%24Cmd.Connection%20%3D%20%24SQLConn%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%24Cmd.CommandTimeout%3D0%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%24Cmd.CommandType%20%3D%20%5BSystem.Data.CommandType%5D%3A%3AStoredProcedure%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%24Cmd.CommandText%20%3D%20%24using%3ASproc%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%24Cmd.ExecuteNonQuery()%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%23%20Close%20the%20SQL%20connection%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%24SQLConn.Close()%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7D%3C%2FP%3E%0A%3CP%3E%7D%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E6.%20Then%20save%20the%20code%20and%20make%20sure%20to%26nbsp%3Bpublish%20it%20after%20saved.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Saroja_L_Sattigeri_13-1642744754092.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F341334i82AF0642C536A84F%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Saroja_L_Sattigeri_13-1642744754092.png%22%20alt%3D%22Saroja_L_Sattigeri_13-1642744754092.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E7.%20To%20add%20a%20Logic%20App%20to%20execute%20the%20Runbook%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EAfter%20trigger-%26gt%3B%20search%20for%26nbsp%3B'%3CSTRONG%3Eautomation'%3C%2FSTRONG%3E%3CI%3E%26nbsp%3B%3C%2FI%3E-%26gt%3B%20select%20the%20'%3CSTRONG%3ECreate%20Job'%20%3C%2FSTRONG%3Eaction%20of%20Azure%20Automation%20connector-%26gt%3B%20Create%20API%20connection-%26gt%3B%20provide%20subscription%2C%20resource%20group%20and%20automation%20account-%26gt%3Bselect%3CSTRONG%3E%20'Yes'%3C%2FSTRONG%3E%20for%3CSTRONG%3E%20Wait%20for%20Job%3C%2FSTRONG%3E%20parameter%20so%20that%20it%20will%20wait%20for%20the%20job%20to%20finish%20before%20completing%20the%20action.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EThen%20click%20on%20%3CSTRONG%3E'add%20new%20parameter'%3C%2FSTRONG%3E%20select%20%3CSTRONG%3E'Runbook'%3C%2FSTRONG%3E%20-%26gt%3B%20select%20your%20Runbook%20which%20you%20have%20created%20for%20executing%20SQL%20stored%20procedure-%26gt%3B%20once%20you%20select%20Runbook%2C%20parameters%20defined%20in%20the%20PowerShell%20script%20will%20be%20shown%20as%20below-%26gt%3B%20Runbook%20Parameter%20SQL%20server%20port%20will%20be%20using%20default%20TCP%20port%201433%20of%20SQL%20server(which%20we%20have%20specified%20in%20PowerShell%20script)%2C%26nbsp%3B%20provide%20values%20for%20'Runbook%20parameter%20Database'%2C%20'Runbook%20Parameter%20SQL%20server'%2C%20'Runbook%20Parameter%20SQL%20credentials'%2C%20'Runbook%20Parameter%20sproc(stored%20procedure%20name)%3A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Saroja_L_Sattigeri_14-1642744754082.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F341333iE278BD0ECF5BF102%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Saroja_L_Sattigeri_14-1642744754082.png%22%20alt%3D%22Saroja_L_Sattigeri_14-1642744754082.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EAfter%20'Create%20job'%20action%2C%20to%20get%20the%20results%20of%20the%20SQL%20stored%20procedure%2C%20add%20'%3CSTRONG%3EGet%20job%20output'%3C%2FSTRONG%3E%20action%20of%20Azure%20automation%20connector-%26gt%3B%20specify%20Subscription%2C%20Resource%20Group%2C%20Automation%20account(same%20automation%20account%20in%20which%20you%20are%20creating%20a%20job)%20and%20use%20%3CSTRONG%3E'Job%20ID'%3C%2FSTRONG%3E%20dynamic%20content%20of%20%3CSTRONG%3E'Create%20job'%3C%2FSTRONG%3E%20action.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Saroja_L_Sattigeri_15-1642744754083.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F341335i15B0B4C64D8253B5%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Saroja_L_Sattigeri_15-1642744754083.png%22%20alt%3D%22Saroja_L_Sattigeri_15-1642744754083.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ETesting%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20confirm%20the%20Logic%20App%20and%20SQL%20stored%20procedure%20does%20not%20timeout%2C%20run%20the%20logic%20app%20and%20the%20output%20of%20the%20stored%20procedure%20will%20be%20shown%20in%20the%26nbsp%3B%3CI%3EGet%20job%20output%3C%2FI%3E%26nbsp%3Baction%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Saroja_L_Sattigeri_16-1642744754084.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F341336iCD9A975113D95F16%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Saroja_L_Sattigeri_16-1642744754084.png%22%20alt%3D%22Saroja_L_Sattigeri_16-1642744754084.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Saroja_L_Sattigeri_17-1642744754085.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F341337i07A5BF28AD3BE431%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Saroja_L_Sattigeri_17-1642744754085.png%22%20alt%3D%22Saroja_L_Sattigeri_17-1642744754085.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ENote%3A%20%3C%2FSTRONG%3ETo%20return%20a%20record%20set%20as%20JSON%20from%20the%20PowerShell%20script%2C%20replace%20the%20code%26nbsp%3B%3CI%3E%24Cmd.ExecuteNonQuery()%26nbsp%3B%3C%2FI%3Ewith%20the%20following%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%24reader%20%3D%20%24Cmd.ExecuteReader()%3C%2FP%3E%0A%3CP%3E%24table%20%3D%20new-object%20%22System.Data.DataTable%22%3C%2FP%3E%0A%3CP%3E%24table.Load(%24reader)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%23--Exporting%20data%20to%20the%20screen%20--%23%3C%2FP%3E%0A%3CP%3E%24table%20%7C%20select%20%24table.Columns.ColumnName%20%7C%20ConvertTo-Json%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3BIf%20you%20return%20a%20very%20large%20record%20set%2C%20you%20may%20exceed%20the%20allocated%20memory%20so%2C%20in%20this%20scenario%2C%20batching%20the%20results%20will%20help.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3CI%3EReferences%3A%3C%2FI%3E%3C%2FSTRONG%3E%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Flogic-apps%2Fhandle-long-running-stored-procedures-sql-connector%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EHandle%20long-running%20stored%20procedures%20in%20the%20SQL%20connector%20-%20Azure%20Logic%20Apps%20%7C%20Microsoft%20Docs%3C%2FA%3E%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3COL%3E%0A%3CLI%3EAzure%20Automation%20Connector%20document%3A%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fconnectors%2Fazureautomation%2F%23actions%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAzure%20Automation%20-%20Connectors%20%7C%20Microsoft%20Docs%3C%2FA%3E%3C%2FLI%3E%0A%3C%2FOL%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3068008%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELogic%20Apps%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jan 20 2022 10:46 PM
Updated by: