Control large SQL result sets and timeouts during stored procedures in workflows for Azure Logic Apps

Applies to: Azure Logic Apps (Consumption + Standard)

To more easily automate business tasks that work with SQL databases, your workflow can use SQL Server connector operations, which provides many backend features for workflows to use in Azure Logic Apps.

In some situations, your workflow might have to handle large result sets. These result sets might be so large that SQL Server connector operations don't return all the results at the same time. In other situations, you might just want more control over the size and structure for your result sets. To organize the results in the way that you want, you can create a stored procedure.

For example, when a SQL Server connector action gets or inserts multiple rows, your workflow can iterate through these rows by using an Until loop that works within these limits. If your workflow must handle thousands or millions of rows, you want to minimize the costs resulting from SQL Server connector action calls to the SQL database. For more information, see Handle bulk data using the SQL connector.

This guide shows how to control the size, structure, and timeouts when processing large result sets using the SQL Server connector actions.

Timeout limit on stored procedure execution

The SQL Server connector has an Execute stored procedure action with timeout limit that's less than two minutes. Some stored procedures might take longer than this limit to complete, which causes a 504 Timeout error. Sometimes long-running processes are coded as stored procedures explicitly for this purpose. Due to the timeout limit, calling such procedures from Azure Logic Apps might create problems.

The SQL Server connector operations don't natively support an asynchronous mode. To work around this limitation, simulate this mode by using the following items:

• SQL completion trigger
• Native SQL pass-through query
• State table
• Server-side jobs

For example, suppose that you have the following long-running stored procedure. To finish running, the procedure exceeds the timeout limit. If you run this stored procedure from a workflow using the SQL Server connector action named Execute stored procedure, you get the HTTP 504 Gateway Timeout error.

CREATE PROCEDURE [dbo].[WaitForIt]
   @delay char(8) = '00:03:00'
AS
BEGIN
   SET NOCOUNT ON;
   WAITFOR DELAY @delay
END

Rather than directly call the stored procedure, you can asynchronously run the procedure in the background by using a job agent. You can store the inputs and outputs in a state table that you can then access and manage through your workflow. If you don't need the inputs and outputs, or if you're already writing the results to a table in the stored procedure, you can simplify this approach.

To asynchronously run the procedure in the background with job agent for cloud-based SQL Server, follow the steps to create and use the Azure Elastic Job Agent for Azure SQL Database.

For on-premises SQL Server and Azure SQL Managed Instance, create and use the SQL Server Agent instead. The fundamental steps remain the same as setting up a job agent for Azure SQL Database.

Create job agent for Azure SQL Database

To create a job agent that can run stored procedures for Azure SQL Database, create and use the Azure Elastic Job Agent. However, before you can create this job agent, you must set up the permissions, groups, and targets as described in the Azure Elastic Job Agent documentation. You must also create a supporting state table in the target database, as described in the following sections.

To create the job agent, perform this task in the Azure portal. This approach adds several stored procedures to the database used by the agent, also known as the agent database. You can then create a job agent that runs your stored procedure in the target database and captures the output when finished.

Create state table for registering parameters and storing inputs

SQL Agent Jobs don't accept input parameters. Instead, in the target database, create a state table where you register the parameters and store the inputs to use for calling your stored procedures. All of the agent job steps run against the target database, but the job's stored procedures run against the agent database.

To create the state table, use this schema:

CREATE TABLE [dbo].[LongRunningState](
   [jobid] [uniqueidentifier] NOT NULL,
   [rowversion] [timestamp] NULL,
   [parameters] [nvarchar](max) NULL,
   [start] [datetimeoffset](7) NULL,
   [complete] [datetimeoffset](7) NULL,
   [code] [int] NULL,
   [result] [nvarchar](max) NULL,
   CONSTRAINT [PK_LongRunningState] PRIMARY KEY CLUSTERED
      (   [jobid] ASC
      )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
      ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Here's how the resulting table looks in SQL Server Management Studio (SMSS):

To ensure good performance and make sure that the job agent can find the associated record, the table uses the job execution ID (jobid) as the primary key. If you want, you can also add individual columns for the input parameters. The previously described schema can more generally handle multiple parameters but is limited to the size calculated by the NVARCHAR(MAX) function.

Create a top-level job to run the stored procedure

To execute the long-running stored procedure, create this top-level job agent in the agent database:

EXEC jobs.sp_add_job 
   @job_name='LongRunningJob',
   @description='Execute Long-Running Stored Proc',
   @enabled = 1

Add steps to the job that parameterize, run, and complete the stored procedure. By default, a job step times out after 12 hours. If your stored procedure needs more time, or if you want the procedure to time out earlier, you can change the step_timeout_seconds parameter to another value that's specified in seconds. By default, a step has 10 built-in retries with a backoff timeout between each retry, which you can use to your advantage.

Here are the steps to add:

1. Wait for the parameters to appear in the LongRunningState table.

   This first step waits for the parameters to get added in LongRunningState table, which happens soon after the job starts. If the job execution ID (jobid) doesn't get added to the LongRunningState table, the step merely fails. The default retry or backoff timeout does the waiting:

   EXEC jobs.sp_add_jobstep
      @job_name='LongRunningJob',
      @step_name= 'Parameterize WaitForIt',
      @step_timeout_seconds = 30,
      @command= N'
         IF NOT EXISTS(SELECT [jobid] FROM [dbo].[LongRunningState]
            WHERE jobid = $(job_execution_id))
            THROW 50400, ''Failed to locate call parameters (Step1)'', 1',
      @credential_name='JobRun',
      @target_group_name='DatabaseGroupLongRunning'
   

2. Query the parameters from the state table and pass them to the stored procedure. This step also runs the procedure in the background.

   If your stored procedure doesn't need parameters, directly call the stored procedure. Otherwise, to pass the @timespan parameter, use the @callparams, which you can also extend to pass more parameters.

   EXEC jobs.sp_add_jobstep
      @job_name='LongRunningJob',
      @step_name='Execute WaitForIt',
      @command=N'
         DECLARE @timespan char(8)
         DECLARE @callparams NVARCHAR(MAX)
         SELECT @callparams = [parameters] FROM [dbo].[LongRunningState]
            WHERE jobid = $(job_execution_id)
         SET @timespan = @callparams
         EXECUTE [dbo].[WaitForIt] @delay = @timespan', 
      @credential_name='JobRun',
      @target_group_name='DatabaseGroupLongRunning'
   

3. Complete the job and record the results.

   EXEC jobs.sp_add_jobstep
      @job_name='LongRunningJob',
      @step_name='Complete WaitForIt',
      @command=N'
         UPDATE [dbo].[LongRunningState]
            SET [complete] = GETUTCDATE(),
               [code] = 200,
               [result] = ''Success''
            WHERE jobid = $(job_execution_id)',
      @credential_name='JobRun',
      @target_group_name='DatabaseGroupLongRunning'
   

Start the job and pass the parameters

To start the job, use a passthrough native query with the Execute a SQL query action and immediately push the job's parameters into the state table. To provide input to the jobid attribute in the target table, Azure Logic Apps adds a For each loop that iterates through the table output from the preceding action. For each job execution ID, run an Insert row action that uses the dynamic data output named ResultSets JobExecutionId to add the parameters for the job to unpack and pass to the target stored procedure.

When the job completes, the job updates the LongRunningState table. From a different workflow, you can trigger on the result by using the trigger named When an item is modified. If you don't need the output, or if you already have a trigger that monitors an output table, you can skip this part.

Create job agent for SQL Server or Azure SQL Managed Instance

For on-premises SQL Server and Azure SQL Managed Instance, create and use the SQL Server Agent. Compared to the cloud-based job agent for Azure SQL Database, some management details differ, but the fundamental steps remain the same.

Next step

• Connect to SQL databases from workflows in Azure Logic Apps