cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
v-yamao-msft
Community Support
Community Support

Hi @Anonymous,

 

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.
Anonymous
Not applicable

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?

Hi @Anonymous,

 

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.
Anonymous
Not applicable

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

Hi @Anonymous,

 

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.
Anonymous
Not applicable

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

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.

Anonymous
Not applicable

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
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Users online (1,184)