在 Azure 逻辑应用的工作流中处理存储过程时控制大型 SQL 结果集和超时

适用于：Azure 逻辑应用（消耗型 + 标准型）

为了更轻松地自动执行处理 SQL 数据库的业务任务，工作流可以使用 SQL Server 连接器操作，这些操作为工作流提供了许多后端功能，供在 Azure 逻辑应用 中使用。

在某些情况下，工作流可能需要处理大型结果集。 这些结果集可能非常大，以至于SQL Server连接器操作无法同时返回所有结果。 在其他情况下，你可能只想更好地控制结果集的大小和结构。 若要按所需方式组织结果，可以创建 存储过程。

例如，当SQL Server连接器操作获取或插入多行数据时，工作流可以使用一个在这些限制内工作的Until循环来遍历这些行。 如果工作流必须处理数千行或数百万行，则希望将 SQL Server 连接器作调用对 SQL 数据库所产生的成本降到最低。 有关详细信息，请参阅 使用 SQL 连接器处理大容量数据。

使用 SQL Server 连接器操作处理大型结果集时，本指南展示了如何控制大小、结构和超时设置。

存储过程执行的超时限制

SQL Server 连接器具有一个执行存储过程操作，超时限制不到两分钟。 某些存储过程可能需要超过此限制才能完成，这会导致 504 超时 错误。 有时，长时间运行的进程将显式编码为存储过程，以实现此目的。 由于超时限制，从 Azure 逻辑应用调用此类过程可能会导致问题。

SQL Server 连接器操作不原生支持异步模式。 若要解决此限制，请使用以下项模拟此模式：

• SQL 自动完成触发器
• 本地 SQL 直通查询
• 状态表
• 服务器端作业

例如，假设你有以下长时间运行的存储过程。 若要完成运行，该过程将超过超时限制。 如果使用名为 Execute stored procedure 的 SQL Server 连接器操作从工作流中运行此存储过程，则会收到 HTTP 504 网关超时错误。

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

可以使用 作业代理在后台异步运行该过程，而不是直接调用存储过程。 可以将输入和输出存储在状态表中，然后可以通过工作流访问和管理这些输入和输出。 如果不需要输入和输出，或者已将结果写入存储过程中的表，则可以简化此方法。

若要使用基于云的 SQL Server 的作业代理在后台异步运行该过程，请执行以下步骤来创建 和使用 Azure SQL 数据库的 Azure 弹性作业代理。

对于本地 SQL Server 和 Azure SQL 托管实例， 请改为创建和使用 SQL Server 代理 。 基本步骤与为 Azure SQL 数据库设置作业代理相同。

为 Azure SQL 数据库创建作业代理

若要创建可运行 Azure SQL 数据库的存储过程的作业代理，请创建和使用 Azure 弹性作业代理。 但是，在创建此作业代理之前，必须按照 Azure 弹性作业代理文档中所述设置权限、组和目标。 还必须在目标数据库中创建支持状态表，如以下部分所述。

若要创建作业代理，请在 Azure 门户中执行此任务。 此方法将多个存储过程添加到代理使用的数据库，也称为 代理数据库。 然后，可以创建一个作业代理，该代理在目标数据库中运行存储过程，并在完成后捕获输出。

创建用于注册参数和存储输入的状态表

SQL 代理作业不接受输入参数。 相反，在目标数据库中，创建一个状态表，在其中注册参数并存储用于调用存储过程的输入。 所有代理作业步骤都针对目标数据库运行，但作业的存储过程针对代理数据库运行。

若要创建状态表，请使用以下架构：

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]

下面是 SQL Server Management Studio（SMSS）中生成的表的外观：

为了确保良好的性能并确保作业代理可以找到关联的记录，表使用作业执行 ID （jobid） 作为主键。 如果需要，还可以为输入参数添加单个列。 前面描述的架构可以更灵活地处理多个参数，但受限于NVARCHAR(MAX)函数计算出的大小。

创建顶层作业以运行存储过程

若要执行长时间运行的存储过程，请在代理数据库中创建此顶级作业代理：

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

将参数化、运行和完成存储过程的步骤添加到作业中。 默认情况下，作业步骤在 12 小时后超时。 如果存储过程需要更多时间，或者希望该过程提前超时，可以将 step_timeout_seconds 参数更改为指定为秒数的其他值。 默认情况下，一个步骤会有 10 次内置重试，每次重试之间都有一个退避超时设置，你可以利用这一点来获取优势。

添加的步骤如下：

1. 等待参数显示在 LongRunningState 表中。

   第一步会等待参数添加到 LongRunningState 表中，这将在作业启动后不久完成。 如果作业执行 ID （jobid） 未添加到 LongRunningState 表中，则步骤只会失败。 默认的重试或退避超时用于等待这一过程完成：

   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. 从状态表查询参数并将其传递给存储过程。 此步骤还会在后台运行该流程。

   如果存储过程不需要参数，请直接调用存储过程。 否则，若要传递 @timespan 参数，请使用 @callparams该参数，也可以将其扩展为传递更多参数。

   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. 完成作业并记录结果。

   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'
   

启动作业并传递参数

若要启动作业，请使用“直通模式”原生查询，并通过“执行 SQL 查询”操作将该任务的参数立即推送到状态表中。 为了向目标表中的 jobid 属性提供输入，Azure 逻辑应用会添加一个 For each 循环，遍历前一步骤的表输出。 对于每个作业执行 ID，请运行一个“插入行”操作，该操作使用命名为 ResultSets JobExecutionId 的动态数据输出来添加作业参数，并将其解压缩后传递到目标存储过程。

作业完成后，作业将更新 LongRunningState 表。 在不同的工作流中，可以使用名为“ 当项目被修改时”的触发器来触发结果。 如果不需要输出，或者已有监视输出表的触发器，则可以跳过此部分。

为 SQL Server 或 Azure SQL 托管实例创建作业代理

对于 本地 SQL Server 和 Azure SQL 托管实例，请创建和使用 SQL Server 代理。 与 Azure SQL 数据库的基于云的作业代理相比，一些管理详细信息有所不同，但基本步骤保持不变。

后续步骤

• 从 Azure 逻辑应用中的工作流连接到 SQL 数据库