cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

Random gateway timeout executing SQL stored procedure.

Hi folks, I have a flow that is timing out 10% of the time in the last week. It used to time out 3% of the time (Thank you Microsoft for analytics!) It is of the template type "When an item is created or modified (in SPO list) Execute stored procedure (in an on prem SQL DB) The trigger step always works, and the successful runs are usually just a few seconds. The procedure has a few parms and essentially stores a plant manager comment for daily operations. Very simple content, and my DBA verifies that the procedure runs like lightning fast. Here are the stats and the body of the failed timeout details. Any opinions would be appreciated. Thanks ! @SmartMeter

 

flow3.pngFlow2.pngflow1.png

The error is as follows, I am thinking there is some resource contention within the gateway (Perhaps it is running on a windows XT box!) or there might be some threading issue within the gateway management that causes resource shortage time outs. What irks me is this thing runs like lightning most of the time, but sometimes it just sits. Very frustrating for the plant management to deal with. Please comment.

 

  "error": {

    "code": 504,

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

    "clientRequestId": "c7836e21-a9eb-4de5-b6db-20b735bf3bb1",

    "message": "BadGateway",

    "innerError": {

      "status": 504,

      "message": "The operation failed due to an explicit cancellation. Exception: System.Threading.Tasks.TaskCanceledException: A task was canceled.

                  \r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.

                  HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.PowerBI.DataMovement.Pipeline.Common.TDFHelpers.<>c__DisplayClass9_0`

                  1.<<GetNextResponse>b__0>d.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n

                  at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.

                  ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n  

                  at Microsoft.PowerBI.DataMovement.Pipeline.Common.TDFHelpers.<>c__DisplayClass13_0.<<ExecuteBlockOperation>b__0>d.MoveNext()\r\n    

                  inner exception: The operation failed due to an explicit cancellation. Exception: System.Threading.Tasks.TaskCanceledException:

                  A task was canceled.\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.PowerBI.DataMovement.Pipeline.Common.TDFHelpers.<>c__DisplayClass9_0`1.<<GetNextResponse>b__0>d.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at Microsoft.PowerBI.DataMovement.Pipeline.Common.TDFHelpers.<>c__DisplayClass13_0.<<ExecuteBlockOperation>b__0>d.MoveNext()\r\n       

 inner exception: A task was canceled.\r\nclientRequestId: c7836e21-a9eb-4de5-b6db-20b735bf3bb1",

      "source": "sqlconnectionprovider-westus.tip0-westus.p.azurewebsites.net"

    }

  }

}

5 REPLIES 5
Highlighted
Community Support
Community Support

Re: Random gateway timeout executing SQL stored procedure.

Hi @SmartMeter,

 

Please take a try to create a new connection on the "Execute stored procedure" action to see if the issue still exists.

Also please make sure the network is in a good condition when the flow runs.

 

Best regards,

Alice

 

 

Highlighted
Helper V
Helper V

Re: Random gateway timeout executing SQL stored procedure.

Thank you alice @v-yuazh-msft I will have our gateway admin create a new connection for our flow, and when it happens next, time, we will do some speed tests and see what things look like on the wan. I appreciate the recommendations!

@SmartMeter

Highlighted
Helper V
Helper V

Re: Random gateway timeout executing SQL stored procedure.

So just a sad update here, nothing seems to mitigate this gateway timeout issue. We have subsequently had four gateway "downtimes." For the sake of correlation, I have looked into timing. It seems to happen between 7:30 am and 9:30 AM. I am wondering at this point, having attempted to blame our LAN and our gateway configuration, perhaps we are off base. Perhaps there is some service that runs on the tenant configuration out in the cloud that is making a necessary resource constraint in the early AM hours. I HAVE seen where our network is acting up and the gateway correspondingly goes offline, but this happens a LOT of the time in the early AM hours. At 7:30 AM in particular. 

Food for thought,

SmartMeter.

Highlighted
Helper V
Helper V

Re: Random gateway timeout executing SQL stored procedure.

We experienced this issue 8 times this morning. Opening a ticket with Microsoft to resolve. Seems like a Gateway code issue, or perhaps it has an issue connecting to SQL during some timeframes (Backups? DB Maint scripts?) Will post solution when Microsoft engages on the issue. We are collection the basic support info this morning on the case.

SmartMeter.

Highlighted
Advocate IV
Advocate IV

Re: Random gateway timeout executing SQL stored procedure.

Hi SmartMeter,

 

Were you able to resolve this with the help of microsoft?

 

Can you please elaborate on the solution if any?

 

Thanks,

 

Michel

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Upcoming Events

Experience what’s next for Power Automate

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Users online (6,800)