cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mokhawaja
Level 8

Error on adding a flow (run SQL query) to powerapps

Hi,

I am building an app connected to Azure SQL database, and i was using flow to retreive large number of records (5000+), so i built a flow using the Get Rows action and Request - Respond, and it was working fine but i noticed it is a little bit slow (takes more than 20 seconds to get ~5000 records), but i got cross @Mr-Dang-MSFT blog of executing a SQL query from powerapps,

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

which was amazing! so i built a new flow and used the same method as Mr. Dang's, and the flow run successfully when i test it inside flow (and it is way faster than Get Rows... for the same table of ~5000 records took 3 seconds!!), but when i try to add it to my app it throws an error:

"Registering error"

Check screenshot.

 

Session ID: 0e3a6b30-f470-4f96-ae96-57ed42eee6b2
PowerApps 3.18052.12

 

i have no problems with other kinds of flow 

Appreciate your help...

 

regards,

Mohammad

1 ACCEPTED SOLUTION

Accepted Solutions
mr-dang
Level 10

Re: Error on adding a flow (run SQL query) to powerapps

Hi @mokhawaja,

Thanks for reaching out. It sounds like your flow is working correctly in Flow, but you are not able to connect it to a button in PowerApps.

 

I encountered something similar when I was originally making the solution. In my case, it turned out that the JSON schema that was in my 'Request - Response' step had a column which did not have its type defined. In the example below, "Context" is the name of one of my columns, but its type is blank:

 

 

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "BankID": {
                "type": "string"
            },
            "Num": {
                "type": "string"
            },
            "Context": {}
        },
        "required": [
            "BankID",
            "Num",
            "Context"
        ]
    }
}

 

Manually typing in the type resolved the issue in my situation. I was able to add the flow to my button after I fixed the schema.

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "BankID": {
                "type": "string"
            },
            "Num": {
                "type": "string"
            },
            "Context": {
                "type": "string"
            }
        },
        "required": [
            "BankID",
            "Num",
            "Context"
        ]
    }
}

 

Why: the column type can sometimes be blank, as it was in my case, when the sample payload that you copy and paste into 'Request - Response' contains a column with null values. If the column does not see any other type of data besides null, it will not know what type of column it is. As a best practice, be sure to generate a large enough sample set of data so that if null values are present in a column, there are other rows in the array that have data.

 

Note: Please note that the 'Request - Response' method has a static schema at this time. This means that if you type a SQL query statement whose expected results do not fit into the columns of your schema, then you will not receive the response you are looking for.

 

Please let me know if this was indeed the case or if you are stil experiencing the error.

 

@Mr-Dang-MSFT

______

Microsoft Employee
@8bitclassroom
3 REPLIES 3
mr-dang
Level 10

Re: Error on adding a flow (run SQL query) to powerapps

Hi @mokhawaja,

Thanks for reaching out. It sounds like your flow is working correctly in Flow, but you are not able to connect it to a button in PowerApps.

 

I encountered something similar when I was originally making the solution. In my case, it turned out that the JSON schema that was in my 'Request - Response' step had a column which did not have its type defined. In the example below, "Context" is the name of one of my columns, but its type is blank:

 

 

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "BankID": {
                "type": "string"
            },
            "Num": {
                "type": "string"
            },
            "Context": {}
        },
        "required": [
            "BankID",
            "Num",
            "Context"
        ]
    }
}

 

Manually typing in the type resolved the issue in my situation. I was able to add the flow to my button after I fixed the schema.

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "BankID": {
                "type": "string"
            },
            "Num": {
                "type": "string"
            },
            "Context": {
                "type": "string"
            }
        },
        "required": [
            "BankID",
            "Num",
            "Context"
        ]
    }
}

 

Why: the column type can sometimes be blank, as it was in my case, when the sample payload that you copy and paste into 'Request - Response' contains a column with null values. If the column does not see any other type of data besides null, it will not know what type of column it is. As a best practice, be sure to generate a large enough sample set of data so that if null values are present in a column, there are other rows in the array that have data.

 

Note: Please note that the 'Request - Response' method has a static schema at this time. This means that if you type a SQL query statement whose expected results do not fit into the columns of your schema, then you will not receive the response you are looking for.

 

Please let me know if this was indeed the case or if you are stil experiencing the error.

 

@Mr-Dang-MSFT

______

Microsoft Employee
@8bitclassroom
mokhawaja
Level 8

Re: Error on adding a flow (run SQL query) to powerapps

Hi @mr-dang,

Awesome! that was exactly the problem... two columns were missing their types, and now it is solved. and by the way running a SQL script action is way much faster than get rows action on SQL as i mentioned (it is even faster than running the script inside SSMS, 7s  in flow vs 31s in SSMS to retrieve a table with 21000+ records!!!)

 

thanks.

Mohammad

Eloy
Level: Powered On

Re: Error on adding a flow (run SQL query) to powerapps

It is also worth mentioning that this will not work on any On-Premise SQL Server due to the On-Premise Gateway not allowing Native SQL Queries. 

See attached error message.

 

P.S I don't know if this is a setting that can be modified.

 

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 183 members 4,081 guests
Please welcome our newest community members: