cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
StevenWade
Advocate III
Advocate III

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

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

3 REPLIES 3
StevenWade
Advocate III
Advocate III

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.

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

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.

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.

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (4,396)