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

Flow suddenly failing at Get Rows SQL step with "The response is not JSON format" error. What to do?

I have a flow that has been working fine for the past three months and then suddenly yesterday it started failing with the error message "The response is not in a JSON format".  Based on the error (below), I've not been able to identify where the problem is. The SQL server is running as expected and the connection is available. Is this a bug? Please help
 
 
{
"error": {
"code": 502,
"source": "flow-apim-europe-001-francecentral-01.azure-apim.net",
"clientRequestId": "5f3fdef9-9db8-400b-8578-c1ed6dbc7bf0",
"message": "The response is not in a JSON format.",
"innerError": "The specified CGI application encountered an error and the server terminated the process."
}
}
 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Regular Visitor

Re: Flow suddenly failing at Get Rows SQL step with "The response is not JSON format" erro

I found the problem.  The problem was that the column name for "State" had changed to "STATE" (i.e. from capitalized to uppercase). We've changed the column name back to 'State' and it is now working again.  It's strange that the Get SQL step didn't detect it as a problem and instead came out with 502 Bad Gateway after 3 minutes of running (instead of the usual 2secs it takes to execute the statement)

{
"error": {
"code": 502,
"source": "flow-apim-europe-001-francecentral-01.azure-apim.net",
"clientRequestId": "8677bcc8-0b15-41dd-8715-8d0534e90ec3",
"message": "The response is not in a JSON format.",
"innerError": "The specified CGI application encountered an error and the server terminated the process."
}
}
 
The problem is of course on our side, but it would be great if Flow could detect the problem and simply say that the column name is wrong, but perhaps this problem is coming from the Data Gataway and not from Flow?
 

View solution in original post

8 REPLIES 8
Highlighted
Community Support
Community Support

Re: Flow suddenly failing at Get Rows SQL step with "The response is not JSON format" erro

Hi @ALChris,

 

What’s your current situation? Are you still having the issue?

Error 502 means that the failure is temporary or transient. Please click Resubmit to try the flow again.

Here is a doc on troubleshooting a flow:

https://docs.microsoft.com/en-us/flow/fix-flow-failures

If the issue still exists, please feel free post back.

 

Best regards,

Mabel

 

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

Re: Flow suddenly failing at Get Rows SQL step with "The response is not JSON format" erro

No the problem is not yet fixed, the error persists when I run the flow.

What can I do? The query works when I run it without criteria, but as soon as I add criteria the error appears. Is this related to the outrages experiences by Microsoft in the past week?
Highlighted
Community Support
Community Support

Re: Flow suddenly failing at Get Rows SQL step with "The response is not JSON format" erro

Hi @ALChris,

 

Could you share a screenshot of your flow to help reproduce this issue?

According to your description, I assume that you are using Filter Query to return items.

How is the function configured for Filter query?

I have made the following flow for testing, which is working fine.

 1.PNG

 

Best regards,

Mabel

 

 

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

Re: Flow suddenly failing at Get Rows SQL step with "The response is not JSON format" erro

Here's my flow:

 

2019-01-29 10_28_17-Edit your flow _ Microsoft Flow.png

 

It was working for a long time and then suddenly Tuesday, it stopped with the error:

 

2019-01-29 10_30_14-Run History _ Microsoft Flow.png

Highlighted
Community Support
Community Support

Re: Flow suddenly failing at Get Rows SQL step with "The response is not JSON format" erro

Hi @ALChris,

 

Could you try to re-configure the function in the Filter query field then update the flow?

According to the screenshot, I have made the following flow with this code which is working properly.

(number eq 80) and (name eq 'test1' or name eq 'test2' or name eq 'test3')

 1.PNG2.PNG

 

Best regards,

Mabel

 

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

Re: Flow suddenly failing at Get Rows SQL step with "The response is not JSON format" erro

I found the problem.  The problem was that the column name for "State" had changed to "STATE" (i.e. from capitalized to uppercase). We've changed the column name back to 'State' and it is now working again.  It's strange that the Get SQL step didn't detect it as a problem and instead came out with 502 Bad Gateway after 3 minutes of running (instead of the usual 2secs it takes to execute the statement)

{
"error": {
"code": 502,
"source": "flow-apim-europe-001-francecentral-01.azure-apim.net",
"clientRequestId": "8677bcc8-0b15-41dd-8715-8d0534e90ec3",
"message": "The response is not in a JSON format.",
"innerError": "The specified CGI application encountered an error and the server terminated the process."
}
}
 
The problem is of course on our side, but it would be great if Flow could detect the problem and simply say that the column name is wrong, but perhaps this problem is coming from the Data Gataway and not from Flow?
 

View solution in original post

Highlighted
Advocate I
Advocate I

Re: Flow suddenly failing at Get Rows SQL step with "The response is not JSON format" erro

This is such a useless error message.
Thanks for figuring it out - I was getting very frustrated. I never would have thought to check the case of my table column.

Highlighted
Regular Visitor

Re: Flow suddenly failing at Get Rows SQL step with "The response is not JSON format" erro

Yeah, especially considering that SQL is not normally case-sensitive. I hope they will improve the error message to avoid frustrated users.

Helpful resources

Announcements
firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

Join the new 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!

Top Solution Authors
Users online (9,501)