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

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.

Highlighted
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

New Ranks and Rank Icons in April

Read the announcement for more information!

firstImage

Better Together Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

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

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Users online (9,062)