cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MJK
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
v-xida-msft
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

6 REPLIES 6
mogulman52
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 

  

v-xida-msft
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.

v-xida-msft
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.
KurtSchron
Advocate I
Advocate I

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

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (1,198)