處理 Azure Logic Apps 的 SQL 連接器中預存程序的逾時

發行項
01/04/2024

適用於：Azure Logic Apps (使用量)

當邏輯應用程式使用的結果集大到 SQL 連接器無法在相同時間傳回所有結果，或者如果您想要進一步控制結果集的大小和結構，您可以建立預存程序，依您想要的方式整理結果。 SQL 連接器提供許多後端功能，可讓您使用 Azure Logic Apps 存取，以便更輕鬆地將使用 SQL 資料庫資料表的商務工作自動化。

例如，在取得或插入多個資料列時，邏輯應用程式可在這些限制內使用 Until 迴圈，逐一查看這些資料列。但是，當邏輯應用程式必須處理數千或數百萬個資料列時，您想要將資料庫的呼叫成本降至最低。如需詳細資訊，請參閱使用 SQL 連接器處理大量資料。

預存程序執行的逾時限制

SQL 連接器有少於 2 分鐘的預存程序逾時限制。有些預存程序可能需要超過此限制才能完成，因而造成 504 Timeout 錯誤。因此，這些長時間執行的程序有時候會明確編碼為預存程序。由於逾時限制，從 Azure Logic Apps 呼叫這些程序可能會產生問題。雖然 SQL 連接器未原生支援非同步模式，但您可以使用 SQL 完成觸發程式、原生 SQL 傳遞查詢、狀態資料表和伺服器端作業來解決此問題並模擬此模式。針對這項工作，您可以使用 Azure SQL Database 的 Azure 彈性作業代理程式。若是內部部署 SQL Server 和 Azure SQL 受控執行個體，您可以使用 SQL Server Agent。

例如，假設您有下列長時間執行的預存程序，其需要花費超過逾時限制的時間才能完成執行。如果您使用 SQL 連接器從邏輯應用程式執行此預存程序，則您得到的結果為 HTTP 504 Gateway Timeout 錯誤。

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

您可以使用「作業代理程式」，以非同步方式在背景中執行預存程序，而不是直接呼叫程序。您可以將輸入和輸出儲存在狀態資料表中，然後您可以透過邏輯應用程式進行互動。如果您不需要輸入和輸出，或如果您已將結果寫入預存程序中的資料表，您可以簡化此方法。

重要

請確定您的預存程序和所有作業都具有「等冪性」，這表示預存程序和所有作業可以多次執行，而不會影響結果。如果非同步處理失敗或逾時，作業代理程式可能會重試步驟，因而多次執行您的預存程序。為避免重複輸出，請先檢閱這些最佳做法和方法，再建立任何物件。

下一節說明如何使用 Azure SQL Database 的 Azure 彈性作業代理程式。若是 SQL Server 和 Azure SQL 受控執行個體，您可以使用 SQL Server Agent。有些管理細節會有所不同，但基本步驟與設定 Azure SQL Database 的作業代理程式相同。

Azure SQL Database 的作業代理程式

若要建立作業以執行 Azure SQL Database 的預存程序，請使用 Azure 彈性作業代理程式。在 Azure 入口網站中建立您的作業代理程式。此方法會將數個預存程序新增至代理程式所使用的資料庫，也稱為「代理程式資料庫」。然後，您可以建立作業，在目標資料庫中執行您的預存程序，並在完成時擷取輸出。

建立作業之前，您必須先設定權限、群組和目標，如 Azure 彈性作業代理程式的完整文件所述。您還必須在目標資料庫中建立支援資料表，如下列各節所述。

建立用來註冊參數和儲存輸入的狀態資料表

SQL Agent 作業不接受輸入參數。而是在目標資料庫中建立狀態資料表，您可以在其中註冊參數和儲存用來呼叫預存程序的輸入。所有代理程式作業的步驟都是針對目標資料庫執行，但是作業的預存程序是針對代理程式資料庫執行。

若要建立狀態資料表，請使用下列結構描述：

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) 的樣貌：

Screenshot that shows created state table that stores inputs for stored procedure.

為確保良好的效能，並確定代理程式作業可以找到相關聯的記錄，資料表使用作業執行識別碼 (jobid) 做為主索引鍵。如有需要，您也可以新增個別資料行以用於輸入參數。以上說明的結構描述通常可以處理更多參數，但受限於 NVARCHAR(MAX) 計算的大小。

建立執行預存程序的最上層作業

若要執行長時間執行的預存程序，請在代理程式資料庫中建立這個最上層的作業代理程式：

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

現在，新增步驟至作業，參數化、執行和完成預存程序。依預設，作業的步驟會在 12 小時後逾時。如果您的預存程序需要更多時間，或您希望程序早點逾時，您可以將 step_timeout_seconds 參數變更為另一個以秒為單位指定的值。依預設，一個步驟內建 10 次重試，每次重試之間都有輪詢逾時，您可以加以利用。

以下是要新增的步驟：

等候參數出現在 LongRunningState 資料表中。

此第一步驟會等候參數加入 LongRunningState 資料表，這會在作業起始之後立即發生。如果作業執行識別碼 (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'

從狀態資料表查詢參數，再將參數傳遞至預存程序。此步驟也會在背景中執行程序。

如果您的預存程序不需要參數，只要直接呼叫預存程序即可。否則，若要傳遞 @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'

完成作業並記錄結果。

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 屬性，Logic Apps 會新增 For each 迴圈，逐一查看來自前一個動作的資料表輸出。針對每個作業執行識別碼，執行 [插入資料列] 動作，使用動態資料輸出 ResultSets JobExecutionId 來新增參數，以使作業能夠解除封裝並傳遞至目標預存程序。

Screenshot that shows actions to use for starting the job and passing parameters to the stored procedure.

作業完成後，作業會更新 LongRunningState 資料表，因此您可以使用 修改項目時觸發程序，輕鬆對結果進行觸發。如果您不需要輸出，或已經有監視輸出資料表的觸發程序，您可以略過此部分。

Screenshot that shows the SQL trigger for when an item is modified.

適用於 SQL Server 或 Azure SQL 受控執行個體的作業代理程式

同樣地，您可以針對內部部署 SQL Server 和 Azure SQL 受控執行個體使用 SQL Server Agent。雖然有些管理細節會有所不同，但基本步驟與設定 Azure SQL Database 的作業代理程式相同。

下一步

連線至 SQL Server、Azure SQL Database 或 Azure SQL 受控執行個體

共用方式為