In Runtime, SQL Server Stored Procedure returns no results

(2) Share

Report

Posted on by gpgeorge

I have an older PowerApps app that I want to update. In the process, I created a Stored Procedure to return a very small records set of 1 to 12 or 13 records to populate a gallery. In the browser development environment, it all works properly. I run the Stored Procedure in the screen's On Visible event. The gallery populates as required. On my phone and tablet, and in the Windows Desktop runtime, it fails silently.

Two other Stored Procs also fail silently. One adds records to the table involved in the process and the other updates them. A dozen other Stored Procs in the app work properly on the runtime devices.

It may be coincidental that the three failing Stored Procs were the most recently created, but I think not.

In trying to resolve this, I've deleted and recreated the connections for all of the Stored Procedures, both those that do work properly and those that fail at runtime multiple times.

I'm looking suggestions as to where I should investigate next.

Please do request additional information. I've not had to trouble-shoot this kind of problems before; I am not sure what details you'll need.

Thanks in advance.

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (0)

Sort by

Suggested answer

VASANTH KUMAR BALMADI 313 on at

Like (4)

Report
Copy link

Link copied!

Hi @gpgeorge

Yeah, this is one of those frustrating cases where everything looks fine in the browser and then just… nothing on mobile.

Since only the newer stored procedures are failing, I’d honestly start by checking permissions on the SQL side. I’ve run into this before where older procedures worked because permissions were already in place, but new ones didn’t have execute rights for the account used by the Power Apps connection. The browser can sometimes mask that depending on how you're connected.

Another thing I’d try is removing the SQL connection from the app entirely and adding it back, not just refreshing it. Power Apps can be a bit weird with newly added stored procedures and doesn’t always pick them up cleanly across all runtimes.

Also, worth checking the stored procedures themselves—make sure they’re returning a clean, consistent result set. If there are things like extra messages or row count outputs, it can cause issues in the player even if it works in the browser. Adding a SET NOCOUNT ON at the top is a quick thing to try.

If you're calling them in OnVisible, maybe try triggering one from a button temporarily and see if it behaves any differently on mobile. Sometimes OnVisible timing can be a bit off in the player.

Given that it’s failing silently, you could also wrap the call in an IfError just to see if anything shows up on mobile—that at least gives you something to go on.

My bet would be either permissions or the connection not fully syncing the new procedures, especially since the rest are working fine.

Was this reply helpful? Yes No
Suggested answer

Valantis 3,256 on at

Like (1)

Report
Copy link

Link copied!

Hi @gpgeorge,

The most likely cause given your description is that the three new stored procedures are not marked as "Safe to use for galleries and tables" in Power Apps.

When you add a stored procedure as a data source in Power Apps, there is a setting underneath it called "Safe to use for galleries and tables".

If this is not checked, Power Apps will not automatically call the procedure to populate a gallery at runtime. In Studio/design mode the behavior can differ because you are calling it explicitly during authoring, but in published runtime on mobile and desktop it silently does nothing.

Here is where to check:
1. In Power Apps Studio, open your app
2. Go to Data > your SQL connection > find the three failing stored procedures
3. Check whether the "Safe to use for galleries and tables" toggle is enabled for each one
4. If not, enable it, save, and republish

Second thing to check: make sure each of the three new stored procedures has SET NOCOUNT ON at the top. Without it, SQL returns row count messages that can confuse the connector and cause silent failures at runtime.

Third: use Power Apps Monitor (App > Monitor in Studio) to run the app and watch the network calls when the OnVisible fires. You will see exactly what the stored procedure returns at runtime and whether there is an error being swallowed silently.

Ref: https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/connections/sql-connection-access-data

Best regards,

Valantis

✅ If this helped solve your issue, please Accept as Solution so others can find it quickly.

❤️ If it didn’t fully solve it but was still useful, please click “Yes” on “Was this reply helpful?” or leave a Like :).

🏷️ For follow-ups @Valantis.

📝 https://valantisond365.com/

💼 LinkedIn

▶️ YouTube

Was this reply helpful? Yes No
timl 36,795 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Hi @gpgeorge

One other thing I'd suggest is to monitor your app.

By doing this, you can verify that the stored procedures are being called, along with any parameter values. If the call to the stored procedure fails, the monitor trace may possibly expose an error message that identifies the cause.

https://learn.microsoft.com/power-apps/maker/monitor-overview

Was this reply helpful? Yes No
gpgeorge 40 on at

Like (0)

Report
Copy link

Link copied!

Thank you. The symptoms you describe match up well with my experience. I'm closing in on the connector and connections as the source of the problem.

First, I am positive the SP resultsets are clean and correct ( as positive as I can reasonably be, at any rate). They return records in the dev environment and in SSMS. As a best practice, I always include SET NOCOUNT ON; I also have an output parameter to feed back an ErrorCode if the Select in the database fails for any reason. That never appears either.

I did add a button as a test to see if that triggered the SP without success; it fails silently and doesn't return the error code. That leads me back to thinking that I've mucked up the connector itself, or the specific connections for these three Stored Procs.

I prefer not to delete the connector, because other apps depend on it. Perhaps I should just create a new one, give it a pristine name and delete all of the connections in order to start fresh with all of the stored procs. I foresee nothing working after that, or everything working. But at this point, I have nothing, really, to lose I suppose.

Was this reply helpful? Yes No
gpgeorge 40 on at

Like (0)

Report
Copy link

Link copied!

Regarding monitoring. I've not done that. Can I turn it on for apps in the runtime environment as well as in the development studio?

Was this reply helpful? Yes No
gpgeorge 40 on at

Like (1)

Report
Copy link

Link copied!

I should have noted that I am always very precise in checking Stored Procs safe for use in galleries and tables when appropriate and did so here.
Forgive me for not including all of the details originally.

Was this reply helpful? Yes No
timl 36,795 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @gpgeorge

It's possible to monitor a published app in the runtime environment.

You'll need to enable the 'Debug published app' option in the settings of your app.

This setting has a negative effect on performance so if this is a live app, you might consider saving a copy of your app and monitoring that.

Was this reply helpful? Yes No
Suggested answer

gpgeorge 40 on at

Like (0)

Report
Copy link

Link copied!

I apologize. I totally biffed this situation. I found my error, and was able to fix it.

By way of explanation, I have a design pattern that requires a hidden screen to remain open so that central code on it runs when called from any active screen. In order to ensure that happens, I have text controls on active screens that monitor a value on the code screen.

It turned out that in the development environment, there was enough memory that kept the hidden screen open WITHOUT that monitoring. As soon as I moved to the runtime on a device, memory management stopped keeping that screen open unless I added that hidden "watcher".

Of course, when I added the 3 new stored procs, I forgot to add their "watchers".

So, it was on me. Lesson learned: Stick to the protocols and never take shortcuts.

Was this reply helpful? Yes No