Azure Logic Apps - SQL tables to a single Email - How To

Question

I have a stored procedure that I'm pulling data sets into Azure Logic Apps Designer. no matter what I try - nothing wants to plug into the send email function. some things that seem like a no brainer => DataOperations => Compose (making a full html table), or Create HTML Table, or Create CSV Table - All create a Foreach loop around the object, then will not in bed into the email - which is HTML code capable.

I can just in bed the body - which is a monstrosity of data, but that's not going to work.

How can I format the SQL data into tables, and send them in a single email. Being able to add a custom entry for a Click me would also be a must.

most of the above was not saving due to error, or would not run. the Data Operations of objects is what I had been working with so far, but I need a working table in an email from the SQL data.

here are the results of the 2 sets of objects that it can spit out, body just being a higher version of result sets it seems. also note this is a single result:

BODY:

{"ResultSets":{"Table1":[{"Title":"Test #3556 - legal","ID":176,"ExecutionDate":"2023-04-27T00:00:00","Division":"Banking","Program":"Banking","ContractType":"Invitation for Bid (\"IFB\")","Name":"BedRock Concrete"}]},"ReturnCode":0,"OutputParameters":{}}





resultsets:
{"Table1":[{"Title":"Test #3556 - legal","ID":176,"ExecutionDate":"2023-04-27T00:00:00","Division":"Banking","Program":"Banking","ContractType":"Invitation for Bid (\"IFB\")","Name":"BedRock Concrete"}]}

so i missed a type of return object -

BODY & Resultsets: gave too much information since it looked like there was a possibility of additional tables to come back. i only called for a single table, so at the bottom of my list I found "Table1" - after using it - it was giving me the results I wanted - a single table representation. This will also allow me to break up the data and give specific header under the CREATE HTML TABLE option.

my end logic app will be Recurance => Execute Stored Procedure => Create HTML Table Feeding in "Table1" objects data => Send Email feeding in CreateHTMLTable.

If the output is an array, it will throw it in a for loop. Can you provide a sample out from the SQL query and maybe we can help with a more targeted answer. Add that output to your question. — Skin, Apr 27 '23 at 21:40

score 1 · Accepted Answer · edited May 02 '23 at 22:29

So I missed a type of return object -

BODY & Resultsets: gave too much information since it looked like there was a possibility of additional tables to come back. I only called for a single table, so at the bottom of my list I found "Table1" - after using it - it was giving me the results I wanted - a single table representation. This will also allow me to break up the data and give specific header under the CREATE HTML TABLE option.

My end logic app will be

Recurance => Execute Stored Procedure => Create HTML Table Feeding in "Table1" objects data => Send Email feeding in CreateHTMLTable.

score 0 · Answer 2 · answered Apr 28 '23 at 07:17

Azure Logic Apps - SQL tables to a single Email - How To

I have reproduced in my environment got expected results:

I have used For Each and faced similar issues, so i alternatively used another method:

enter image description here

Output: enter image description here

enter image description here Logic app code:

{
    "definition": {
        "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
        "actions": {
            "Create_HTML_table": {
                "inputs": {
                    "format": "HTML",
                    "from": "@body('Execute_a_SQL_query_(V2)')?['resultsets']?['Table1']"
                },
                "runAfter": {
                    "Execute_a_SQL_query_(V2)": [
                        "Succeeded"
                    ]
                },
                "type": "Table"
            },
            "Create_HTML_table_2": {
                "inputs": {
                    "format": "HTML",
                    "from": "@body('Execute_a_SQL_query_(V2)')?['resultsets']?['Table2']"
                },
                "runAfter": {
                    "Execute_a_SQL_query_(V2)": [
                        "Succeeded"
                    ]
                },
                "type": "Table"
            },
            "Execute_a_SQL_query_(V2)": {
                "inputs": {
                    "body": {
                        "query": "SELECT TOP (1000) * FROM [dbo].[emo1]\nSELECT TOP (1000) * FROM [dbo].[emo]"
                    },
                    "host": {
                        "connection": {
                            "name": "@parameters('$connections')['sql']['connectionId']"
                        }
                    },
                    "method": "post",
                    "path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('default'))},@{encodeURIComponent(encodeURIComponent('default'))}/query/sql"
                },
                "runAfter": {
                    "Initialize_variable": [
                        "Succeeded"
                    ]
                },
                "type": "ApiConnection"
            },
            "Initialize_variable": {
                "inputs": {
                    "variables": [
                        {
                            "name": "ammo",
                            "type": "array"
                        }
                    ]
                },
                "runAfter": {},
                "type": "InitializeVariable"
            },
            "Send_an_email_(V2)": {
                "inputs": {
                    "body": {
                        "Body": "<p>Tables:<br>\n@{body('Create_HTML_table')}<br>\n<br>\n@{body('Create_HTML_table_2')}</p>",
                        "Importance": "Normal",
                        "Subject": "TABLES",
                        "To": "v-rbojja@microsoft.com"
                    },
                    "host": {
                        "connection": {
                            "name": "@parameters('$connections')['office365']['connectionId']"
                        }
                    },
                    "method": "post",
                    "path": "/v2/Mail"
                },
                "runAfter": {
                    "Create_HTML_table": [
                        "Succeeded"
                    ],
                    "Create_HTML_table_2": [
                        "Succeeded"
                    ]
                },
                "type": "ApiConnection"
            }
        },
        "contentVersion": "1.0.0.0",
        "outputs": {},
        "parameters": {
            "$connections": {
                "defaultValue": {},
                "type": "Object"
            }
        },
        "triggers": {
            "manual": {
                "inputs": {
                    "schema": {}
                },
                "kind": "Http",
                "type": "Request"
            }
        }
    },
    "parameters": {
        "$connections": {
            "value": {
                "office365": {
                    "connectionId": "/subscriptions/b823f/resourceGroups/rbojja/providers/Microsoft.Web/connections/office365",
                    "connectionName": "office365",
                    "id": "/subscriptions/b83c1ed/providers/Microsoft.Web/locations/eastus/managedApis/office365"
                },
                "sql": {
                    "connectionId": "/subscriptions/b83c1ed3f/resourceGroups/rbojja\/providers/Microsoft.Web/connections/sql-3",
                    "connectionName": "sql-3",
                    "id": "/subscriptions/b83c1ed3-c5b\/providers/Microsoft.Web/locations/eastus/managedApis/sql"
                }
            }
        }
    }
}

Azure Logic Apps - SQL tables to a single Email - How To

2 Answers2