Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

The response is not on JSON format when reading all records

Hello everyone,

I have a really complex flow that extracts info from sharepoint lists, passes it to a SQL Server through a Data Gateway and then writes the result of a saved query view to a Microsoft Lists. The flow first goes through the whole list and deletes it, and then extracts info from the SQL Server and writes it again.


The thing is the flow worked like a charm when I tested it using at most 300 records, but now that I am asking to use the full capacity, it either fails in the step of "deleting items from the list" or "writing items on the list". And the most annoying part is that it always fails in one random record with the "The response is not on JSON format" error. The record is always a different one.


My flow execution errors are:

When deleting:


The output error is: 

{"statusCode":400,"headers":{"X-SharePointHealthScore":"2","SPRequestGuid":"a3750bbd-dedd-4eab-bf6d-20c96816e729","request-id":"a3750bbd-dedd-4eab-bf6d-20c96816e729","MS-CV":"vQt1o93eq06/bSDJaBbnKQ.0","Strict-Transport-Security":"max-age=31536000","X-FRAME-OPTIONS":"SAMEORIGIN","MicrosoftSharePointTeamServices":"","X-Content-Type-Options":"nosniff","X-MS-InvokeApp":"1; RequireReadOnly","Timing-Allow-Origin":"*","x-ms-apihub-cached-response":"true","Cache-Control":"private","Date":"Thu, 22 Apr 2021 16:42:08 GMT","P3P":"CP=\"ALL IND DSP COR ADM CONo CUR CUSo IVAo IVDo PSA PSD TAI TELo OUR SAMo CNT COM INT NAV ONL PHY PRE PUR UNI\"","X-AspNet-Version":"4.0.30319","X-Powered-By":"ASP.NET","Content-Length":"1937","Content-Type":"application/json"},"body":{"error":{"code":400,"source":"","clientRequestId":"a3750bbd-dedd-4eab-bf6d-20c96816e729","message":"The response is not in a JSON format.","innerError":"<?xml version=\"1.0\" encoding=\"utf-8\"?>\r\n<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"\">\r\n<html xmlns=\"\">\r\n  <head>\r\n    <title>Request Error</title>\r\n    <style>BODY { color: #000000; background-color: white; font-family: Verdana; margin-left: 0px; margin-top: 0px; } #content { margin-left: 30px; font-size: .70em; padding-bottom: 2em; } A:link { color: #336699; font-weight: bold; text-decoration: underline; } A:visited { color: #6699cc; font-weight: bold; text-decoration: underline; } A:active { color: #336699; font-weight: bold; text-decoration: underline; } .heading1 { background-color: #003366; border-bottom: #336699 6px solid; color: #ffffff; font-family: Tahoma; font-size: 26px; font-weight: normal;margin: 0em 0em 10px -20px; padding-bottom: 8px; padding-left: 30px;padding-top: 16px;} pre { font-size:small; background-color: #e5e5cc; padding: 5px; font-family: Courier New; margin-top: 0px; border: 1px #f0f0e0 solid; white-space: pre-wrap; white-space: -pre-wrap; word-wrap: break-word; } table { border-collapse: collapse; border-spacing: 0px; font-family: Verdana;} table th { border-right: 2px white solid; border-bottom: 2px white solid; font-weight: bold; background-color: #cecf9c;} table td { border-right: 2px white solid; border-bottom: 2px white solid; background-color: #e5e5cc;}</style>\r\n  </head>\r\n  <body>\r\n    <div id=\"content\">\r\n      <p class=\"heading1\">Request Error</p>\r\n      <p>The server encountered an error processing the request. See server logs for more details.</p>\r\n    </div>\r\n  </body>\r\n</html>"}}}

And when the error happens when writing data into the Sharepoint List:



I have tried everything, the worst part is that if you try again the same flow (which takes about 2 hours to run completely) it fails somewhere else and in a different record.


I alse checked if bot JSON items retrieved before deleting/writing are correctly built.

Any help or suggestion is really appreciated


Helpful resources

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!


Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Users online (64,641)