Power Automate Execute Stored Procedure (V2) Failure Bad Gateway and Timeout

[RickEpstein]

We are having an issue with scheduling a stored procedure with Power Automate.

There is a default of 120 seconds for SQL execution.  If there is no message coming back from SQL Server during that time, Power Automate terminates the process.  In other words, it is not executed asynchronously. Our stored procedure takes at least 30 minutes to run and there is no way that it can send a response to Power Automate before it completes.

We need to find a way to change this setting to be greatly increased (ideally unlimited). 

This is the error message that we get when running the process “Execute Stored Procedure (V2)” in a flow.  How can we adjust the timeout setting?

{

    "statusCode": 504,

    "headers": {

        "Pragma": "no-cache",

        "x-ms-datasourceerror": "True",

        "x-ms-request-id": "a3f4f525-b024-40c0-8c27-3894e4df111b",

        "Strict-Transport-Security": "max-age=31536000; includeSubDomains",

        "X-Content-Type-Options": "nosniff",

        "X-Frame-Options": "DENY",

        "Timing-Allow-Origin": "*",

        "x-ms-apihub-cached-response": "true",

        "x-ms-apihub-obo": "true",

        "Cache-Control": "no-store, no-cache",

        "Date": "Mon, 25 Jul 2022 16:39:45 GMT",

        "Content-Length": "687",

        "Content-Type": "application/json",

        "Expires": "-1"

    },

    "body": {

        "error": {

            "code": 504,

            "source": "flow-apim-msmanaged-na-eastus-01.azure-apim.net",

            "clientRequestId": "a3f4f525-b024-40c0-8c27-3894e4df111b",

            "message": "BadGateway",

            "innerError": {

                "status": 504,

                "message": "Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.\r\nclientRequestId: a3f4f525-b024-40c0-8c27-3894e4df111b",

                "error": {

                    "message": "Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."

                },

                "source": "sql-eus.azconn-eus.p.azurewebsites.net"

            }

        }

    }

}

[v-xiaochen-msft · Accepted solution]

Hi @RickEpstein ,

Have a good day. I'm not an expert in SQL Server. 

I researched the official documentation and found it to be a limitation. 

SQL Server - Connectors | Microsoft Docs

Long-Running SQL Stored Procedures in LogicApps - Microsoft Tech Community

The documentation seems to indicate that the workaround is to configure an agent.

Hope it can helps you.

Best Regards,

Wearsky