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"
}
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 :
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"
}
}
}
}
} ```
I got this from my existing project.
This is the action
I have a previously created API connection for my SQL Server database.
Action body
Run History
Output body (DB Record)
After getting the DB record, I sent an email based on the returned DB record.
For To section
For Subject Section