cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Flow throws an error when SQL query returns no data

Hi,

 

I have written a flow that retrieves a set records from SQL table based on an input from powerapps. The flow runs fine when the query returns data, but when the query doesn't return any data it throws an error. How do I fix that? As it is not actually an error.

 

Also, I am storing the result of the flow in a collection in powerapps and passing it to the Items property of a Gallery. So when the flow returns data the Gallery loads pretty quickly (1-2 sec),  but when the flow does return any data it takes time for the Gallery to load(8-9 sec).

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Hi @MJK ,

According to the error message that you mentioned, I think this issue is related to you flow action. Currently, I could not come up a better solution to fix this issue, it's an known limit with the SQL Query in SQL Server connector.

 

If you would like this feature to be improved in PowerApps or Power Automate, please consider submit an idea to PowerApps Ideas Forum:

https://powerusers.microsoft.com/t5/Power-Apps-Ideas/idb-p/PowerAppsIdeas

 

As an temporary solution, I think adding a empty record in your SQL Query result could fix your issue. The Empty record in your SQL Query result would not affect the data display in your canvas app. Within your canvas app, you could use the Filter function to filter out this empty record, and display the actual retrieved result.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Helper I
Helper I

Just a thought.  Could you make it so query always returns at least 1 record. Example:

 

SELECT [CompanyName],
       [CompanyPhone],
       [Website]
FROM   [dbo].[Company]
UNION ALL
SELECT 'Blank' AS CompanyName,
       ''      AS CompanyPhone,
       ''      AS Website 

  

Community Support
Community Support

Hi @MJK ,

Could you please share a bit more about your error message?

 

Based on the issue that you mentioned, I think this an known limit in SQL Server connector -- The result set schema after executing SQL queries and stored procedures should contain unique non-empty column names. Please check the following known limits in SQL Server connector:

https://docs.microsoft.com/en-us/connectors/sql/#known-issues-and-limitations

 

I agree with @mogulman52 's thought almost. You should make sure your SQL Query or SQL Stored procedure return one record at least, so that, the "Execute a SQL Query" action or the "Execute stored procedure" action would not raise an error.

 

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

https://community.adobe.com/t5/coldfusion/sql-add-blank-row-to-result/td-p/772119?page=1

Modify your SQL Query as below:

 

select Column1, Column2, Column3 from [dbo].[SQLTable]
UNION ALL
select '' as Column1, '' as Column2, '' as Column3

 

Within your canvas app, you could filter out this blank record using Filter function as below (set the Items property of the Gallery) :

 

Filter(CollectionTable, !IsBlank(Column1) && !IsBlank(Column2) && !IsBlank(Column3))

 

Note: The empty record added in your SQL Query result would affect the data retrieved from your flow into your canvas app.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-xida-msft ,

This is the error I am getting - 

Issue3.png

 

Also, returning an empty record is not an option for me. It will further add different complication in my app.

 

Thanks.

Community Support
Community Support

Hi @MJK ,

According to the error message that you mentioned, I think this issue is related to you flow action. Currently, I could not come up a better solution to fix this issue, it's an known limit with the SQL Query in SQL Server connector.

 

If you would like this feature to be improved in PowerApps or Power Automate, please consider submit an idea to PowerApps Ideas Forum:

https://powerusers.microsoft.com/t5/Power-Apps-Ideas/idb-p/PowerAppsIdeas

 

As an temporary solution, I think adding a empty record in your SQL Query result could fix your issue. The Empty record in your SQL Query result would not affect the data display in your canvas app. Within your canvas app, you could use the Filter function to filter out this empty record, and display the actual retrieved result.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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 Kudoed Authors
Users online (47,842)