powerapp reports Error "NoResponse" from Flow when sql query returns no results

[AlanCampbell]

HI all,

   I've created a powrapp with a flow that is being used to pass a SQL query to an Azure SQL database, this works fine and returns data, however if the query doesn't return any data I get an error, this occurs every time the query returns no results.  Any thoughts on how I can stop this from popping up, as there is no issue, just no data to be shown

[v-xida-msft]

Hi @AlanCampbell,

Could you please share a bit more about your scenario?

Further, could you please show more details about the formula within your app?

If you want to stop this error message from popping up, I think the IfError() function could achieve your needs.

I have made a test on my side, please take a try with the following workaround:

Set the OnSelect property of the Button control to following formula:

IfError(
  SQLQueryMemberActivity.Run(),
  Notify("No data returned!",NotificationType.Warning)
)

Note: If you want to use IfError function within your app, you must turn on/enable the "Formula-leave error management" option within the Advanced Settings of App settings firstly (App settings-> Advanced Settings-> "Formula-leave error management"). 

More details about the IfError function in PowerApps, please check the following article:

IfError function

Best regards,

Kris

[hugovankeulen]

I have the same problem. If the Flow/SQL is returning an empty result, a red error popup is shown on the mobile devices, however not in the browser of a desktop/laptop.

The IfError solution is working on the desktop\browser but not on the mobile apps.

The solution is to make a query that is always returning at least one record. You have to add a 'dummy' record and remove that record from the local collection. You can do this with T-SQL command: UNION ALL

Example:

This is the sql query that  is put in the label LabelSQLQuery:

"SELECT z.Project as Project,sum(z.aantaluur) as toturen

FROM

(SELECT d.Project,d.Aantaluur FROM doorgeboekteurenperpersoonpowerapps as d WHERE (d.Jaar+d.Week/100) >= " & (TextJaarStartFlow.Text) & "."  & Left(Text(100*Value(TextWeekStartFlow.Text)/100;"[$-nl-NL]0#");2)  & " And (d.Jaar+d.Week/100) <= " & TextJaarEindFlow.Text & "." &  Left(Text(100*Value(TextWeekEindFlow.Text)/100;"[$-nl-NL]0#");2) & " And d.Persoon = '" & DropdownPersoonPeriodeFlow.Selected.Persoon

& "' UNION ALL SELECT 'dummy project' ,0) as z"

& "  group by z.Project order by z.Project"

The part UNION ALL SELECT 'dummy project' ,0 adds a “ dummy”  record to the result. So there is always at least 1 record returned by the sql server.

The flow is called from a button with the code:

ClearCollect(queryResults2;GetSQLQuery.Run(LabelSQLQuery.Text));;Remove( queryResults2;First( Filter ( queryResults2; Project="dummy project" ) ) )

The dummy record is removed from the collection with :

Remove( queryResults2;First( Filter ( queryResults2; Project="dummy project" ) ) )

If the dummy record is the only record from the query you see the record for a moment in the gallery before it is deleted. If there are more records, you cannot notice this at all.

I hope this helps and if someone has a more elegant solution, please let me know

[TopShelf-MSFT]

adding in @GregLi and @JonL-MSFT to review and assist with this SQL/Flow question. 

@TopShelf-MSFT