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

[ALChris]

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."
}
}

[v-yamao-msft]

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

[ALChris]

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?

[v-yamao-msft]

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.

 

Best regards,

Mabel

[ALChris]

Here's my flow:

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

[v-yamao-msft]

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')

 

Best regards,

Mabel

[ALChris · Accepted solution]

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?

[GeriRichter]

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.

[ALChris]

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