cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

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

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
Frequent Visitor

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
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

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.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Users online (5,188)