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\nbody{margin:0;font-size:.7em;font-family:Verdana, Arial, Helvetica, sans-serif;background:#EEEEEE;}\r\nfieldset{padding:0 15px 10px 15px;} \r\nh1{font-size:2.4em;margin:0;color:#FFF;}\r\nh2{font-size:1.7em;margin:0;color:#CC0000;} \r\nh3{font-size:1.2em;margin:10px 0 0 0;color:#000000;} \r\n#header{width:96%;margin:0 0 0 0;padding:6px 2% 6px 2%;font-family:\"trebuchet MS\", Verdana, sans-serif;color:#FFF;\r\nbackground-color:#555555;}\r\n#content{margin:0 0 0 2%;position:relative;}\r\n.content-container{background:#FFF;width:96%;margin-top:8px;padding:10px;position:relative;}\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"
}
}
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.
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
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: