Issue executing a SQL SP with a response to PoweApps from Flow

[doubles]

I am trying to get the results of a SQL stored procedure to appear in a data table in a PowerApp by following along with this blog post: https://flow.microsoft.com/en-us/blog/howto-upload-return-file/. Everything appears to work as expected, except that no data is being returned in the collection in my PowerApp -- the collection has the correct field headings, and I know there are results being returned by the SP (the results are appearing correctly in the outputs section when i test the Flow), but there are no data records in the collection. Any help would be appreciated!

[v-xida-msft · Accepted solution]

Hi @doubles,

Based on the screenshot that you provided, I think there may be something issue with your flow's configuration.

I have made a test on my side, and don't have the issue that you mentioned. The screenshot as below:

Flow's configuration:

Within the "Response" action, set the Body field to following formula (WDL expression):

body('Execute_stored_procedure')?['ResultSets']?['Table1']

Set the Response Body JSON Schema field to following:

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "DepartId": {
                "type": "integer"
            },
            "DepartName": {
                "type": "string"
            }
        },
        "required": [
            "DepartId",
            "DepartName"
        ]
    }
}

The flow executes successfully as below:

More details about using expression in flow actions, please check the following blog:

https://flow.microsoft.com/en-us/blog/use-expressions-in-actions/

App's configuration:

The queryRequests collection show well as below:

Please check and see if the following blog would help in your scenario:

https://powerapps.microsoft.com/en-us/blog/return-an-array-from-flow-to-powerapps-response-method/

Please take a try to modify your flow or re-create a new flow based on above blog, then try it again, check if the issue is solved.

Note: Within Microsoft Flow, the "Execute stored procedure" action of SQL Server connector has same output schema with the "Execute a SQL query" action, so you could apply the solution within above blog into your flow.

Best regards,

Kris

[doubles]

@v-xida-msft -- thanks so much, this worked perfectly.

[AnnaCICI_Z]

Hello, I did exactly as Kris said, but PowerApps records collection always gets Value = True.

How do you get the table result ?  Do you modify collection setting ?

[shubh]

Same Problem with me .. Please let me know if you find anyway to make it happen.

[DavesTechTips]

Hi @doubles 

Below are two videos dealing with advanced queries to SQL from PowerApps (using Flow).
For SQL Azure you can use direct (native) queries:
https://youtu.be/DII10gK715I

For on-premesis SQL servers, native queries are not supported from Flow (you will get an error: operation Execute Native Sql is currently not supported using an on-prem gateway connection), so we have to use stored procedures to accomplish the same:
https://youtu.be/BAGBzI4zdww

Please let me know if you don't come right.

Dawid van Heerden
Follow on Twitter: @davestechtips
Subscribe to YouTube: https://www.youtube.com/davestechtips?sub_confirmation=1
**If you found this reply helpful, please mark this as the answer to close the topic and make it easier to find for other people with similar questions.