azure-logic-apps

Azure Logic App returns metadata instead of SQL query results when invoked via HTTP trigger


I'm working with an Azure Logic App that is supposed to execute a SQL query and return the results when triggered via an HTTP GET request. However, instead of receiving the SQL query results, I'm getting metadata or properties of the response.

When I check the run history in the Azure Portal, I see the correct SQL query results, which look like this:

{
  "ResultSets": {
    "Table1": [
      {
        "PRODUCT_KEY": "2771497f-6b51-4607-af12-b81f946c9752",
        "INVENTORY_ITEM_ID": 300001679044591,
        "ITEM_NUMBER": "278",
        "ITEM_DESCRIPTION": "Ring, key; tamper-proof...",
        // ... additional fields ...
      }
    ]
  },
  "OutputParameters": {}
}

However, when I invoke the Logic App via the HTTP GET URL, I receive a response that contains metadata about the Logic App run instead of the SQL query incorrect results:

{
  "properties": {
    "status": "Running",
    "outputs": {},
    "response": {
      "status": "Waiting"
    },
    // ... additional metadata ...
  },
  "id": "/workflows/...",
  "name": "08584732105684415113523970809CU31",
  "type": "Microsoft.Logic/workflows/runs"
}

Logic Apps

Code :

Checked the Response Action Configuration:

Ensured that the Response action is set to return the body of the SQL query action. Removed any operationOptions to make sure the response is synchronous. Adjusted the Response Action Expression:

Modified the expression in the Response action to specifically reference the SQL query results: java Copy code @body('Execute_a_SQL_query_(V2)').ResultSets.Table1 i have tried most of this :

Response Body

Verified the HTTP Trigger Settings:

Confirmed that the Asynchronous Response option is disabled in the HTTP trigger settings. Tested the Logic App with a Simple Response:

Changed the Response action to return a static message, which works correctly when invoked. Checked the Run History:

No errors are reported; the Logic App run shows successful execution with the correct outputs in the run history.

We need to figure out how to use the query result so that api get calls has the data

Code :

{
"definition": {
 "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
 "contentVersion": "1.0.0.0",
 "triggers": {
   "When_a_HTTP_request_is_received": {
     "type": "Request",
     "kind": "Http",
     "inputs": {
       "method": "GET",
       "schema": {}
     }
   }
 },
 "actions": {
   "Execute_a_SQL_query_(V2)": {
     "type": "ApiConnection",
     "inputs": {
       "host": {
         "connection": {
           "name": "@parameters('$connections')['sql-1']['connectionId']"
         }
       },
       "method": "post",
       "body": {
         "query": "select * from dwh.dim_product where item_number=\n'278'"
       },
       "path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('syn-dlr-eda-prd.sql.azuresynapse.net'))},@{encodeURIComponent(encodeURIComponent('Syndw'))}/query/sql"
     },
     "runAfter": {}
   },
   "Response": {
     "type": "Response",
     "kind": "Http",
     "inputs": {
       "statusCode": 200,
       "body": "@triggerOutputs()?['queries']"
     },
     "runAfter": {
       "Execute_a_SQL_query_(V2)": [
         "Succeeded"
       ]
     },
     "operationOptions": "Asynchronous"
   },
   "Compose": {
     "type": "Compose",
     "inputs": "@body('Execute_a_SQL_query_(V2)')?['resultsets']?['Table1']",
     "runAfter": {
       "Response": [
         "Succeeded"
       ]
     }
   }
 },
 "outputs": {},
 "parameters": {
   "$connections": {
     "type": "Object",
     "defaultValue": {}
   }
 }
},
"parameters": {
 "$connections": {
   "value": {
     "sql-1": {
       "id": "/subscriptions/c6119c4d-7517-4020-96e2-e98vjfj5d52/providers/Microsoft.Web/locations/southcentralus/managedApis/sql",
       "connectionId": "/subscriptions/c6119c0-96e2-e985d5c5xoxe2/resourceGroups/RG-DWH-DEV/providers/Microsoft.Web/connections/sql-1",
       "connectionName": "sql-1"
     }
   }
 }
}
} ```

Solution

  • I got this from my existing project.

    This is the action

    enter image description here

    I have a previously created API connection for my SQL Server database.

    Action body

    enter image description here

    Run History

    enter image description here

    enter image description here

    Output body (DB Record)

    enter image description here

    After getting the DB record, I sent an email based on the returned DB record.

    enter image description here

    enter image description here

    For To section

    enter image description here

    For Subject Section

    enter image description here