cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
KroonOfficeSol 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
KroonOfficeSol 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
mr-dang 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
Islam
New Member

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

A

KroonOfficeSol 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

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

sarveshshinde15 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
firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Automate Community!

firstImage

Now Live: Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Users online (8,613)