cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Stored Procedure - ['Table1'] is of type 'Null'

Hi

 

I am successfully executing a stored proceedure via teh data gateway within a flow. it is returning a row of data which I am also successfully parsing from JSON to an object using the "Parse JSON" action.

 

The "Table1" field is showing as an array of objects 

{
  "Table1": [
    {
      "key1": 123,
      "key2": 456
    }
  ]
}

 

 

However when I try to iterate this array with an "Apply to each" action, the flow errors with:

"The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@body('Parse_JSON')?['ResultSets']?['Table1']' is of type 'Null'. The result must be a valid array."

 

I have tried array(body('Parse_JSON')?['ResultSets']?['Table1'])

 

I can see the data, how can I process it if flow believes "Table1" is null?sproc.png

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Stored Procedure - ['Table1'] is of type 'Null'

Hi @michaelwoodew ,

 

You can try to store the content that needs to be configured in Content field in an Object variable, and then populate the variable in Content field.

 

Best Regards,

Community Support Team _ Barry
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

Highlighted
Frequent Visitor

Re: Stored Procedure - ['Table1'] is of type 'Null'

Thanks @v-bacao-msft 

Your suggestion pointed me to a working work-around. 

 

I'm passing ResultSet from the stored proceedure into an object var, then JSON Parsing that (aleady) object. The JSON Parse is so that I can provide it with a schema so that I can easily select the values in the foreach in later steps.

ParseJson.jpg

 

Thanks

View solution in original post

5 REPLIES 5
Highlighted
Dual Super User III
Dual Super User III

Re: Stored Procedure - ['Table1'] is of type 'Null'

Hi @michaelwoodew 

 

Can you please share the following details:

1. Schema that you are using the parse json action

2. A screenshot of what you are getting in the run history of teh Parse JSON action. 

 

 

Highlighted
Community Support
Community Support

Re: Stored Procedure - ['Table1'] is of type 'Null'

Hi @michaelwoodew ,

 

Please check whether Body of Execute stored procedure action contains property-Table1.

Or check if Body of Parse JSON contains Table1 property.

 

Parse JSON is suitable for parsing JSON data with fixed properties. The properties name is not dynamically changed.

 

For example, the following data is used to generate Schema, but the parsed content does not contain Table1 at the end, so there will be problems when referring.

{
  "Table1": [
    {
      "key1": 123,
      "key2": 456
    }
  ]
}

Image reference:

13.PNG14.PNG

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: Stored Procedure - ['Table1'] is of type 'Null'

Hi @v-bacao-msft,

 

Thanks for your guide, 

I can confirm that the JSON Schema is set Expecting the exact Table1 structure that the procedure returns and shows a successful output. 

 

ParseJson.jpgParseJson2.jpg

Highlighted
Community Support
Community Support

Re: Stored Procedure - ['Table1'] is of type 'Null'

Hi @michaelwoodew ,

 

You can try to store the content that needs to be configured in Content field in an Object variable, and then populate the variable in Content field.

 

Best Regards,

Community Support Team _ Barry
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

Highlighted
Frequent Visitor

Re: Stored Procedure - ['Table1'] is of type 'Null'

Thanks @v-bacao-msft 

Your suggestion pointed me to a working work-around. 

 

I'm passing ResultSet from the stored proceedure into an object var, then JSON Parsing that (aleady) object. The JSON Parse is so that I can provide it with a schema so that I can easily select the values in the foreach in later steps.

ParseJson.jpg

 

Thanks

View solution in original post

Helpful resources

Announcements
FirstImage

Microsoft Ignite 2020

Check out the announcement of Power Platform content at Microsoft Ignite!

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

Users online (5,723)