Power Platform Community Forum Thread Details

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:

Spoiler (Highlight to read)

{"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":"16.0.0.21207","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":"flow-apim-msmanaged-na-centralus-01.azure-apim.net","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\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">\r\n<html xmlns=\"http://www.w3.org/1999/xhtml\">\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: ; 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>"}}}

{"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":"16.0.0.21207","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":"flow-apim-msmanaged-na-centralus-01.azure-apim.net","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\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">\r\n<html xmlns=\"http://www.w3.org/1999/xhtml\">\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: ; 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

Categories:

Using flows