Flow throws an error when SQL query returns no data

[MJK]

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!

[mogulman52]

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 

[v-xida-msft]

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,

[MJK]

@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.

[v-xida-msft · Accepted solution]

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,

[KurtSchron]

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

[MJK]

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