Flow throws an error when SQL query returns no data

(0) Share

Report

Posted on by MJK

109

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!

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Sort by

mogulman52 41 on at

Like
a
(0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
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,

Was this reply helpful? Yes No
MJK 109 on at

Like
a
(0)

Report
Copy link

Link copied!

@v-xida-msft ,
This is the error I am getting -

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

Thanks.

Was this reply helpful? Yes No
Verified answer

v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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,

Was this reply helpful? Yes No
KurtSchron 43 on at

Like
a
(0)

Report
Copy link

Link copied!

Any updates to this? This is really a bad situation to have to add empty rows just to return a no result.

Was this reply helpful? Yes No
MJK 109 on at

Like
a
(0)

Report
Copy link

Link copied!

@KurtSchron Nope, that is the only solution as far as I know

Was this reply helpful? Yes No
gbarbeck 4 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you for the help on this thread! I solved this problem without adding a blank row in SQL. If results sets are null, use a second respond to power apps or response step with same parameter names , value, and count . Set all values to null. The flow will generate a blank row without touching the SQL.

Was this reply helpful? Yes No