cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bwieland86
Level: Powered On

Potential Bug: Retrieving data from SQL Azure stored procedure using Flow using Response method recently broke

I want to use PowerApps to call a stored procedure in SQL Azure to retrieve data from my database. I used the Response method, documented here: https://powerapps.microsoft.com/en-us/blog/return-an-array-from-flow-to-powerapps-response-method/ by @Mr-Dang-MSFT 

 

and it was working earlier last week, but suddenly stopped working and am now getting the following error in Flow:

 

Flow Response Error.jpg

 

I was following the methods documented of setting the Body to body('Execute_a_SQL_query').ResultSets.Table1 in the Expression fx field, as seen here:

 

Flow Response Setup.jpg

I know this is not an issue in the stored procedure as I get the intended data when I simply run EXECUTE <spName> @parameter1 = val1, @parameter2 = val2, @parameter3 = val3, and I am not getting an error in the "Execute Stored Procedure" step of the Flow, when it is run without the Response connector.

When I tried to use ResultSet, the Flow runs successfully, but I am unable to pull the actual data into PowerApps. I just receive the table headers:

 

Flow Response using ResultsSet.jpgPowerApps Store Flow Data into Collection.jpgPowerApps Run Flow Result.jpg

Any help on how to resolve this new bug?

 

My current workaround is to create a separate staging table, create a stored procedure to transfer and transform the relevant data, then create a view to pull the data into PowerApps, then use a timer to call the initial transfer and transform stored procedure in the OnTimerStart and then load the contents of the view into a Collection in the OnTimerEnd event, effectively making the timer my Promise method. This is .... far from ideal, as I would like to directly capture the data from the transfer and transform stored procedure into a Collection in PowerApps. With the timer method, I cannot guarantee that the data will be transfered within the 5 second interval I am providing the timer run job.

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Power Apps Staff Mr-Dang-MSFT
Power Apps Staff

Re: Potential Bug: Retrieving data from SQL Azure stored procedure using Flow using Response method recently broke

@bwieland86

It looks like the error is appearing because it's looking for an action called 'Execute a SQL query', but does not see it. 

 

From your flow, you have an action 'Execute stored procedure (V2)'. 

 

Can you rename the instances of any expressions that are pointing to 'Execute_a_SQL_query' to 'Execute_stored_procedure_(V2)'. Use the dynamic content to make sure it's typed out the way that hit should be.

 

I also notice that in the input to your Response, it is feeding in the schema starting at Table instead of drilling into ResultSets. Can you check the expression you have in the Body?

View solution in original post

2 REPLIES 2
Power Apps Staff Mr-Dang-MSFT
Power Apps Staff

Re: Potential Bug: Retrieving data from SQL Azure stored procedure using Flow using Response method recently broke

@bwieland86

It looks like the error is appearing because it's looking for an action called 'Execute a SQL query', but does not see it. 

 

From your flow, you have an action 'Execute stored procedure (V2)'. 

 

Can you rename the instances of any expressions that are pointing to 'Execute_a_SQL_query' to 'Execute_stored_procedure_(V2)'. Use the dynamic content to make sure it's typed out the way that hit should be.

 

I also notice that in the input to your Response, it is feeding in the schema starting at Table instead of drilling into ResultSets. Can you check the expression you have in the Body?

View solution in original post

Highlighted
bwieland86
Level: Powered On

Re: Potential Bug: Retrieving data from SQL Azure stored procedure using Flow using Response method recently broke

That worked! I couldn't find it in the dynamic content, but used what you wrote and it worked as expected. Now I can undo all the Promise timers that I created as my workaround... thanks!

Helpful resources

Announcements
thirdimage

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors
Users online (4,781)