CODEWITHSUNDEEP
sql-server-2016azure-logic-apps
Satya Azure
Satya Azure

Reputation: 479

Logic Apps - Bad Gateway 502 executing SQL Server stored procedure

I am trying to execute a stored procedure on-premise which inserts 1000 records at a time from a Logic App. I have tested the stored procedure separately and it takes less than a second to insert the data and the I have the on-premise gateway connection tested as well from the Logic Apps with another procedure. When I run the Logic Apps which gets the data from another web service in a JSON format and passed to the on-premise SQL Server stored procedure, I am getting a bad gateway (502) error.

I do see a similar question, but it's not answered on Stack Overflow. As I don't see much helpful information from the error, can anyone advise how to troubleshoot this issue? All I see is the command is not supported. I appreciate the help!

{
  "error": {
    "code": 502,
    "source": "logic-apis-centralus.azure-apim.net",
    "clientRequestId": "xxxxxxx",
    "message": "BadGateway",
    "innerError": {
      "status": 502,
      "message": **"The command '**[   AzureConnection = [gateway=\"true\",server=\"xxx.com\",database=\"xxx\"],\r\n    request = [Connection = AzureConnection],\r\n    dataSet = \"default\",\r\n    procedure = \"[dbo].[UpsertEventData]\",\r\n    parameters = [jsonObject=[\r\n  {\r\n    \"eventId\": \"a835795db0f7a13ab03be8e41bde7f56bc9b772905d82e7c111252d998a002be\",\r\n    \"startTime\": \"2018-09-26T00:23:06+00:00\",\r\n    \"endTime\": \"2018-09-26T00:24:50+00:00\",\r\n    \"userId\": \"6aa080743b27d1a9a67afd2683ede38a\",\r\n    \"channelId\": \"987fb87a4f7b4d867c1f391d38ec98b2\",\r\n    \"shareId\": null,\r\n    \"deviceId\": \"bdf8ada28095551a705737900d7c5bf3\",\r\n    \"divisionId\": null,\r\n,\r\n    \"contactId\": null,\r\n    \"type\": \"asset-in-app-viewed\",\r\n    \"page\": 1\r\n  },  **isn't supported.**\r\n     **inner exception:** The command '[   AzureConnection = [gateway=\"true\",server=\"xxx.com\",database=\"xxx\"],\r\n    request = [Connection = AzureConnection],\r\n    dataSet = \"default\",\r\n    procedure = \"[dbo].[UpsertEventData]\",\r\n    parameters = [jsonObject=[\r\n  {\r\n    \"eventId\": \"a835795db0f7a13ab03be8e41bde7f56bc9b772905d82e7c111252d998a002be\",\r\n    \"startTime\": \"2018-09-26T00:23:06+00:00\",\r\n    \"endTime\": \"2018-09-26T00:24:50+00:00\",\r\n    \"userId\": \"6aa080743b27d1a9a67afd2683ede38a\",\r\n    \"channelId\": \"987fb87a4f7b4d867c1f391d38ec98b2\",\r\n    \"shareId\": null,\r\n    \"deviceId\": \"bdf8ada28095551a705737900d7c5bf3\",\r\n    \"divisionId\": null,\r\n    \"assetId\": \"44ecbff1f7427e1fdc2bc3e1ad47f827\",\r\n    \"contactId\": null,\r\n    \"type\": \"asset-in-app-viewed\",\r\n    \"page\": 1\r\n  },\r\n  {\r\n    \"eventId\": \"a835795db0f7a13ab03be8e41bde7f5666f3195ab9eaf651a159d6ce22b04cc7\",\r\n    \"startTime\": \"2018-09-26T00:25:45+00:00\",\r\n    \"endTime\": \"2018-09-26T00:25:49+00:00\",\r\n    \"userId\": \"6aa080743b27d1a9a67afd2683ede38a\",\r\n    \"channelId\": \"7ae82...**' isn't supported.**\r\nclientRequestId: 7d9cbf4d-b50f-4905-8825-554f7e48d54c",
      "source": "sql-logic-cp-centralus.logic-ase-centralus.p.azurewebsites.net"
    }
  }
}

Upvotes: 0

Views: 4145

Answers (1)

Satya Azure
Satya Azure

Reputation: 479

Finally I am able to fix the issue with some debugging on the Logic Apps and Stored Proc.

Fix: The issue is with passing the 'Parse JSON' output from Logic Apps as an input to the Stored Procedure. I used the string conversion function to fix the issue.

I wish Logic Apps future release would give more specific exception details instead of a generic bad gateway error.

Upvotes: 1

Related Questions