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

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

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,727)