I have a problem with the sql execute stored procedure element in flow, which doesn't return the output parameters and returncode.
The issue occurs only when connecting to an on premise sql server through the data gateway and not when using a sql server in Azure.
The returned data with Azure
{ "OutputParameters": { "outputDec": 880, "OutputInt": 1, "OutputStr": "SO71774" }, "ReturnCode": 1, "ResultSets": { "Table1": [{}]
The returned dataset with the Data Gateway
{ "OutputParameters": {}, "ResultSets": { "Table1": [{}]
The main issue is that I need to use a condition to determine if
"Table1": [{}]
exist in my resultsets. I use a respons request to return the results to PowerApps and if Table1 does not exists my flow (and PowerApp) break. The condition if false should fix this.
Questions:
Solved! Go to Solution.
He @mr-dang, Thanks for you're respons.
I thought off the length() solutions, but found out it didn't work. The reason is that ['Table1'] doesn't exist in the respons so the flow breaks on that parameter. I then tried the length() without the ['Table1'] part, but this gave an error that it expects a string and not a object or array. Then I found the solution which is:
@not(empty(body('Execute_stored_procedure')?['resultsets']))
This works for the condition and fixes the flow.
I have now another challenge at the side off PowerApps and the ClearCollect() function. This function gives a 503 error when there is no returned table. For the browser this is no problem, but on a mobile you get an red error message. Maybe you could help me with a fix for this challenge?
I think off two lines off solutions:
Hope you can help.
Hi @KroonOfficeSol,
I am thinking you can attempt to do a count of the rows using an expression with length().
Here's a simplified example of using length
Here I initialized an array variable, but I didn't put anything in it. Then in the compose step, I used an expression to count how many records was in the array--we're expecting 0.
length(variables('test'))
In your case, you'll want to check a condition if the length is 0, then perform actions then.
equals(length(body('name_of_your_step')['ResultSets']['Table1']),0)
Let me know if that or part of that works for you.
Brian
A
He @mr-dang, Thanks for you're respons.
I thought off the length() solutions, but found out it didn't work. The reason is that ['Table1'] doesn't exist in the respons so the flow breaks on that parameter. I then tried the length() without the ['Table1'] part, but this gave an error that it expects a string and not a object or array. Then I found the solution which is:
@not(empty(body('Execute_stored_procedure')?['resultsets']))
This works for the condition and fixes the flow.
I have now another challenge at the side off PowerApps and the ClearCollect() function. This function gives a 503 error when there is no returned table. For the browser this is no problem, but on a mobile you get an red error message. Maybe you could help me with a fix for this challenge?
I think off two lines off solutions:
Hope you can help.
For everybody who comes to this threat. As a solution to this issue I added a extra record to my select statement at the end off the stored procedure, for this I use the formula
Union All Select Null,Null, Null, Null, Null
(as much Nulls as the number off columns to return).
So in the case there are no records in my select statement I still return a resultsets with a table1 containing the one record. Then I remove the last record from my collection in PowerApps right after the ClearCollect(). So with no results the collection will be empty, but the flow runs without errors.
OffCourse still like to get a solution to repress the 503 error message in PowerApps if possible.
@KroonOfficeSol Nice alternative.
I am also facing same issue and wants to supress the error shown in PowerApps.
Hopefully there is better way to supress the error. Even I tried using IfError function. That also didnt help. It still shows badgateway error when there are no rows returned from your store proc.
Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.
Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!
User | Count |
---|---|
15 | |
10 | |
3 | |
3 | |
3 |