cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SmartMeter
Level 8

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
Community Support Team
Community Support Team

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

 

 

SmartMeter
Level 8

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
SmartMeter
Level 8

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.

SmartMeter
Level 8

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.

MichelH
Level 8

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
firstImage

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

firstImage

Incoming: New and improved badges!

Look out for new contribution recognition badges coming SOON!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors
Users online (6,203)