Unanswered

502 - Get rows failed on SQL connector - 'Response not in JSON format'

(0) Share

Report

Posted on by StevenWade

129

Running into an issue on a SQL Get Rows connector. I'm connecting to a SQL database via a gateway, and getting a 502 fail when attempting to return records with an ODATA filter.

I have been successful previously. I can connect to other tables in the same database and perform ODATA queries via the connector. I'm using the same db.owner service account for all the connections I make to this database.

I can even connect to the table causing the issue and pull back records using a Top Count to limit records returned. But, when I try to run ODATA queries against the problem table, I get the following error.

{
  "error": {
    "code": 502,
    "source": "flow-apim-msmanaged-na-westus2-01.azure-apim.net",
    "clientRequestId": "b27ad04a-b8cd-417d-a104-18f436aa2f41",
    "message": "The response is not in a JSON format.",
    "innerError": "<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Strict//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd\">\r\n<html xmlns=\"http://www.w3.org/1999/xhtml\">\r\n<head>\r\n<meta http-equiv=\"Content-Type\" content=\"text/html; charset=iso-8859-1\"/>\r\n<title>502 - Web server received an invalid response while acting as a gateway or proxy server.</title>\r\n<style type=\"text/css\">\r\n\r\n</style>\r\n</head>\r\n<body>\r\n<div id=\"header\"><h1>Server Error</h1></div>\r\n<div id=\"content\">\r\n <div class=\"content-container\"><fieldset>\r\n  <h2>502 - Web server received an invalid response while acting as a gateway or proxy server.</h2>\r\n  <h3>There is a problem with the page you are looking for, and it cannot be displayed. When the Web server (while acting as a gateway or proxy) contacted the upstream content server, it received an invalid response from the content server.</h3>\r\n </fieldset></div>\r\n</div>\r\n</body>\r\n</html>\r\n"
  }
}

Categories:

Using connectors

I have the same question (0)

All responses (3)

Answers (0)

StevenWade 129 on at

Like (0)

Report
Copy link

Link copied!

Wanted to provide an update. This morning, I worked with my SQL Admin to run a tracer on this and discovered that the GET ROWS wasn't actually making it to the SQL database for the unsuccessful ODATA queries, which led me to focus on the SQL Connector itself.

I created a new SQL Connection and new Test Flow, and the ODATA queries now work as expected. So progress!

However, I cannot use the new connection in an existing flow, because the GET ROW then gives me a different error:

InvalidTemplate. Unable to process template language expressions in action 'Get_rows' inputs at line '1' and column '2443': 'The template language expression 'json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_sql_1']['connectionId']' cannot be evaluated because property 'shared_sql_1' doesn't exist, available properties are 'shared_sharepointonline, shared_sql'. Please see https://aka.ms/logicexpressions for usage details.'.

This issue is documented elsewhere--apparently the solution is to rebuild the flow from scratch.

Anyway, there seems to be some sort of defect within the SQL connector that limits reuse. Thought you'd like to know.

Was this reply helpful? Yes No
v-yamao-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @StevenWade,

Thanks for updating and sharing the detailed info.

It seems that creating a new SQL connection works now.

Do the two connections have the same name? Before it returns the error message, have you updated the credentials of Gateway/on-Premises SQL Server?

What’s the code you are using for filter query?

Best regards,

Mabel Mao

Was this reply helpful? Yes No
StevenWade 129 on at

Like (0)

Report
Copy link

Link copied!

Code for the filter ODATA query was a simple "fieldname eq 'targettext"' against an nvarchar column.

I named the connections differently, and I did review and update credentials for both the Gateway and the onprem SQL.

Was this reply helpful? Yes No