SQL Stored Procedure Call from PowerApps | Missing all my stored procedures

Like (0) Share

Report

Posted on 15 Oct 2024 13:25:50 by Warren-GenPoint

Hi,

I have a PowerApp connecting to on-premise SQL via a gateway. All is good, I am able to read and write to any table and call any view. We decided to start trying to use Stored Procedures for the obvious performance benefits.

But, I cannot see any stored procedures other than what appears to be a few built-in ones.

These are the type of stored procedures I can see when selecting (Showing when I start typing sp_)

Here is when you don't search, shows about 56 stored procs, none of my own custom ones.

These are the ones I really want to see.

Does anyone have any ideas here or had a similar issue and how did you resolve it? I am 100% I am using the correct SQL connection as I am seeing all the tables and views.

Thanks,

Warren

Categories:

Power Platform Build Tools

All responses (9)

Answers (0)

MichaelFP 1,831 Super User 2025 Season 1 on 15 Oct 2024 at 15:34:37

Like (0)

Report

SQL Stored Procedure Call from PowerApps | Missing all my stored procedures

Are you using Datagateway for that? If it is, try to remove and include the SQL connection again in power apps.
Suggested answer

Warren-GenPoint 55 on 15 Oct 2024 at 15:04:09

Like (0)

Report

SQL Stored Procedure Call from PowerApps | Missing all my stored procedures

@Pstork1 - turns out my account didnt have execute stored procedures permissions on test database. You DID mention that earlier. Thanks!
Warren-GenPoint 55 on 15 Oct 2024 at 14:33:47

Like (0)

Report

SQL Stored Procedure Call from PowerApps | Missing all my stored procedures

oh no....bug? I am seeing all sprocs in powerautomate via same gateway
Warren-GenPoint 55 on 15 Oct 2024 at 14:28:45

Like (0)

Report

SQL Stored Procedure Call from PowerApps | Missing all my stored procedures

Thats great idea. I will try that also.
Pstork1 66,015 Most Valuable Professional on 15 Oct 2024 at 14:20:51

Like (0)

Report

SQL Stored Procedure Call from PowerApps | Missing all my stored procedures

The only other thing I can suggest is to try creating a Power Automate flow and see if the Stored Procedures are visible there. If they aren't then its definitely a problem with authentication through the connector. If they do then its some kind of bug in the Power Apps support. Stored Procedures in Power Apps is a very new feature.
Warren-GenPoint 55 on 15 Oct 2024 at 14:06:37

Like (0)

Report

SQL Stored Procedure Call from PowerApps | Missing all my stored procedures

Hi,

Okay I get what you mean, its using SQL authentication. Same account I used to create the procs.
Pstork1 66,015 Most Valuable Professional on 15 Oct 2024 at 14:03:28

Like (0)

Report

SQL Stored Procedure Call from PowerApps | Missing all my stored procedures

I understand that they are your Stored Procedures. But the connector is probably running using a different account. If it is then the stored procedures would only be visible if that account has access to the stored procedures. When you set up the SQL connection what type of authentication did you use?
Warren-GenPoint 55 on 15 Oct 2024 at 13:53:19

Like (0)

Report

SQL Stored Procedure Call from PowerApps | Missing all my stored procedures

Hi, I created the stored procs.
Pstork1 66,015 Most Valuable Professional on 15 Oct 2024 at 13:41:03

Like (1)

Report

SQL Stored Procedure Call from PowerApps | Missing all my stored procedures

Are you running the SQL connection as your account? Or using a SQL authentication? Could it be that the account used in the connection doesn't have permissions for the Stored Procedures?