cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resident Rockstar
Resident Rockstar

SQL Execute Stored Procedure - Data Gateway - returns no parameters and returncode

 

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:

  1. is this issue by design off the data gateway and is it impossible to return the parameters and returncode? Or is this a bug and should it be normal behaviour that this items should be returned to flow?
  2. Is there a workaround in which I can check if the ResultSets contain the Table1? I this way I can base my condition on Table1 to exists or not.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resident Rockstar
Resident Rockstar

Re: SQL Execute Stored Procedure - Data Gateway - returns no parameters and returncode

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:

  1. PowerApps - is there a way to catch and repress the errormessage (so don't show it to the user)
  2. Flow - is there a way to create a ['Table1'], which contains the headers from my sql output, but with no record.

Hope you can help.

View solution in original post

5 REPLIES 5
Highlighted
Community Champion
Community Champion

Re: SQL Execute Stored Procedure - Data Gateway - returns no parameters and returncode

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
test - array length.png

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

Microsoft Employee
@8bitclassroom
Highlighted
New Member

Re: SQL Execute Stored Procedure - Data Gateway - returns no parameters and returncode

A

Highlighted
Resident Rockstar
Resident Rockstar

Re: SQL Execute Stored Procedure - Data Gateway - returns no parameters and returncode

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:

  1. PowerApps - is there a way to catch and repress the errormessage (so don't show it to the user)
  2. Flow - is there a way to create a ['Table1'], which contains the headers from my sql output, but with no record.

Hope you can help.

View solution in original post

Highlighted
Resident Rockstar
Resident Rockstar

Re: SQL Execute Stored Procedure - Data Gateway - returns no parameters and returncode

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.

Advocate IV
Advocate IV

Re: SQL Execute Stored Procedure - Data Gateway - returns no parameters and returncode

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

Helpful resources

Announcements
secondImage

August 2020 CYST Winners!

Check out the winners of the recent 'Can You Solve These?' community challenge!

thirdImage

Experience what's new for Power Automate

Join us for an in-depth look at the new Power Automate features and capabilities at the free Microsoft Business Applications Launch Event.

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Users online (4,233)