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

[bwieland86]

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:

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:

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:

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!

[Mr-Dang-MSFT · Accepted solution]

@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?

[bwieland86]

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!