Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Andy Tippin]

I’m trying to pull in SQL data to a PowerApp but my SQL data is the output from a stored procedure.
It sounds like I need to use a Flow in conjunction with PowerApps to accomplish this. 
I’ve got my on-prem SQL setup on a data gateway and that’s working fine. 
I can create my flow ok: Triggered by PowerApps button, set static params just for testing. But the output options are very limited. 
Using the “Respond to PowerApp” step, the only option I have in dynamic content is the Return Code. Which, by the way, comes back as an empty string.
So I get no visible output in my app. I can check the flow status and see the output but there’s nothing useful in there.

I’ve tested the sproc in SSMS with the same creds and it works fine. Returns 179 records.

Any ideas?

——————————
Andy Tippin
——————————

[Will Page]

You can return complex data back to PowerApps with this method: https://powerapps.microsoft.com/en-us/blog/return-an-array-from-flow-to-powerapps-response-method/

——————————
Will Page
Technical consultant
Christchurch, NZ
——————————
——————————————-

[Andy Tippin]

I was follow This link to do the stored procedures.
I tried the method you said and tried just running a SQL query with the Execute statement to run the sproc. I got some error stating it’s not supported through the on-prem gateway.

I did run across this which might suggest that, even though I don’t see the data in the response, it may be present. 

You should be able to get the procedure results with the following expression under Microsoft Flow:

body('<Action_name>')?['ResultSets']
Which you may reference the detailed value by adding:

body('<Action_name>')?['ResultSets']?['FieldName']?

When I run my flow, I do get a ResultsSets in my output but it appears to be empty. But maybe it’s just indicating it’s an array without showing me what’s in it?

I just can’t figure out how to get that expression to work to reference the output from the previous step.

——————————
Andy Tippin
——————————
——————————————-

[Brent Mason]

As a work around, if it is an option, you could modify to stored procedure to write it’s output to a SQL table and then read the input via standard SQL connector and PowerApps Filter/Lookup etc.

——————————
Brent Mason
Project Manager
5037995914
——————————
——————————————-

[Steve Bolton]

One thing you may want to check is to verify the Stored Procedure has SET NOCOUNT ON.  This prevents the query from returning record count statistics that can confuse Power BI. Not sure how it affects Power Apps.

CREATE procedure [dbo].[sp_JobsForecastByWeek]
AS
SET NOCOUNT ON

——————————
Steve Bolton
Sr. Business Analyst
——————————
——————————————-

[Andy Tippin]

 
Thanks for the reply. I double checked, and the sproc does indeed have SET NOCOUNT ON.
I’ve contacted our DBA to see if he can output to a table that I can just pull from but haven’t heard anything back yet. It’s just really annoying that it works for the guy on the internet but doesn’t work for me.?

——————————
Andy Tippin
——————————
——————————————-

[Marcello Brocchi]

Hi Andy,

I recently posted a video that covers the subject of using SQL and Power Apps and includes methods on how to retrieve data correctly from Power Automate and use that data within Power Apps. Here’s a link – 

https://youtu.be/awsNLPGNI4w

——————————
Marcello Brocchi
Software Engineer
——————————
——————————————-

[Andy Tippin]

That was very helpful, thank you.
Unfortunately, I’m still not getting any output from my sproc and I have no idea why. My SQL is on-prem accessed through a gateway. The db and sproc list populates which tells me there’s no connection/authentication issues…

I test and it says it ran successfully but I have nothing in my results set. I run the sprocs in SSMS and they work fine. I’ve even tried tweaking them to return less than 500 rows thinking that might be an issue.. no joy.?

——————————
Andy Tippin
——————————
——————————————-