Execute Stored Procedure infinite loop

[Developer1SG]

Hi

When I Run stored procedure from PowerApps it goes in infinite loop. It's working fine when I run it from SQL Management studio.

I have an other procedure in the same app and it works fine. This procedure was working fine weak ago.

appreciate your help.

@storedProcedure

[Pstork1]

Are you using a Flow to call the Stored Procedure?  Calling SQL Stored Procedures directly from Power Apps isn't supported.  The following is from the known limitations section of the SQL connector documentation found here: https://docs.microsoft.com/en-us/connectors/sql/#known-issues-and-limitations 

1. Stored Procedures cannot be invoked from Power Apps.
   • One workaround is to create a flow and invoke the flow from Power Apps.

[Developer1SG]

Thanks for your replay.

I use Flow to Call the Stored Procedure.

I create Flow for every Stored procedure I use in the Application and they are working, but this one doesn't.

I test the flow and pass parameters then it keep running many times. when I run the Stored procedure from SQL direct It run one time as expected.

[Pstork1]

Can you provide screenshots of the flow that runs the stored procedure?

[Developer1SG]

4- keep retrying (4 times till now)1- flow design2- flow Run3- keep running until I restart SQL service

[Pstork1]

Is the SQL server located on-premises?  The error causing the retry is a Gateway error.  The problem is in the connection between Power Automate and the SQL server.  Its not in a loop, the stored procedure is failing to run and retrying.

[timl]

I agree with @Pstork1  - the "gateway timeout" status in your screenshot confirms this.

As a first step, I would try restarting your on premise gateway server. When the server restarts, I would check that the service is actually started though the Windows control panel > services. If it still doesn't work, I'd then check the Windows event log/on-premise gateway logs.

[Developer1SG]

Yes, SQL Server located on-premises. It seems that it fail to run the procedure, but actually it's run it and fill the table with the same data each time it try. may be I have to troubleshoot Gateway issues as @timl  said. 

Thanks.

[Pstork1]

If you run the stored procedure on the server manually how long does it take?  According to your screenshot when running it via flow it takes over 2 minutes.  That's a long time for a stored procedure.  It shouldn't take any longer to run via flow than it does manually.  If it takes almost 2 minutes to run you may need to tune the stored procedure for better performance.  That may be why its timing out.  I also noticed you are using SQL Express rather than a full version of SQL.  That may cause timing issues as well if the stored procedure is doing a lot of work.