cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MichelH
Advocate V
Advocate V

SQL stored procedure on-premises data gateway timeout

I found  I can run an SQL stored procedure on the local SQL server and it is very handy when the procedures are short lived. However, when the time the procedure takes to run, exceeds 2 minutes, the Flow seems to do a couple of extra things that I do not understand.

 

When I manually run the SP that I want to launch via Flow, it takes 5 to 7 minutes to run, where it basically pulls data from a number of sources (linked servers) via a local view, and combines them into a local table. This always works as expected, there is no issue with the SP.

 

When I run the SP via Flow it does do the first part of the flow, where it drops the existing table, and then things become unclear. After 2 minutes it signals a timeout, and from then on it seems to hammer the SQL server, since my SQL studio seems to have a hard time getting any response. The flow and the sever then go in limbo for about 30 minutes and seemly do nothing but jamming.

 

The flow is running with my personal credentials, the same as I use in the SQL Studio. 

 

Is there anything I can do to prevent this timeout and retry after 2 minutes?

Is there anything I can do to prevent the explicit cancellation?

Any suggestions to make this work as expected?

 

I did look at and adjusted the timeout settings, but I think the 'Note' tells me it will not work. 

Timeout:  Limit the maximum duration an asynchronous pattern may take.
Note: this does not alter the request timeout of a single request.

 

 

GatewayTimeout.jpg

 

The error message:

 

 
{
"error": {
"code": 504,
"source": "flow-apim-europe-001-northeurope-01.azure-apim.net",
"clientRequestId": "bba12345-a123-b456-c789-cf64d495e8d1",
"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__DisplayClass7_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__DisplayClass11_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__DisplayClass7_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__DisplayClass11_0.<<ExecuteBlockOperation>b__0>d.MoveNext()\r\n inner exception: A task was canceled.\r\nclientRequestId: bba12345-a123-b456-c789-cf64d495e8d1",
"source": "sql-ne.azconn-ne.p.azurewebsites.net"
}
}
}

 

The stored procedure:

DROP TABLE IF EXISTS [LocalDB].[dbo].[MH_test_dev]

SELECT * INTO [LocalDB].[dbo].[MH_test_dev] FROM [LocalDB].[dbo].[MH_OpenSO_dev] 

 

 

Seems someone has marked my post as SPAM,....  😞   Notified moderator but no response.

 

 

Thanks for any feedback,

 

Michel

1 REPLY 1
Shegs
Advocate II
Advocate II

Any update on this? I have the same issue and am having a hell of time figuring out a workaround to just trigger the SP and let it ride.

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

New Process Advisor Capabilities carousel.png

Read the blog for the latest news

Read the latest about new experiences and capabilities in the Power Automate product blog.

PA Survey Carousel Image.png

We want to hear from you!

If you are a small business ISV/Reseller, share your thoughts with our research team.

AI Builder AMA June 7th carousel (up on May 25th, take down June 8th) (1).png

'Ask Microsoft Anything' about AI Builder!

The AI Builder team invite you to ask questions and provide helpful answers at our next AMA.

Top Kudoed Authors
Users online (1,478)