cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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
Power Apps

@Anonymous, 

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
Power Apps

@Anonymous, 

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

Anonymous
Not applicable

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
secondImage

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

Top Solution Authors
Top Kudoed Authors
Users online (9,386)