Skip to main content

Notifications

Power Automate - Using Connectors
Unanswered

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

(0) ShareShare
ReportReport
Posted on by 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\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"
  }
}

  • StevenWade Profile Picture
    StevenWade 129 on at
    Re: 502 - Get rows failed on SQL connector - 'Response not in JSON format'

    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.

  • v-yamao-msft Profile Picture
    v-yamao-msft on at
    Re: 502 - Get rows failed on SQL connector - 'Response not in JSON format'

    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

  • StevenWade Profile Picture
    StevenWade 129 on at
    Re: 502 - Get rows failed on SQL connector - 'Response not in JSON format'

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,580

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,909

Leaderboard

Featured topics