Execute a SQL Query. Just started to explore Flow today so still trying to find the best places to get to know more and more about this extremely promising product. Most of my needs are based on SQL Server data. However, I didn't see any triggers under SQL Server or ability to run a SQL query. Once you are Microsoft Flow, select the account menu and navigate to the “My Connections”. Click “Add a Connection” button in the top right corner and then select SQL server. Select the “Connect via on premise data gateway” checkbox. Next, provide the server name, database name, credentials and make sure the correct gateway is selected. Power Query lets you reshape, transform and clean data from SQL Server or Azure SQL Databases so you can use it in your automations. You guessed it, saving time and money. Making Power Automate even more powerful.

Last Friday while checking the Twitter I found a twitter from Jon Levesque asking if someone had a sample on how to took SQL data table results and passed a Markdown-formatted table into an Approval Request. By coincidence, two weeks ago, I was helping a colleague of mine doing something similar, in that case, it was just adding a well-formatted table with the results of an SQL expression to a notification email. Two different but similar scenarios.

To be able to create this simple proof-of-concept you should:

• Access the Flow portal: https://flow.microsoft.com/ and sign in with your account
• In flow.microsoft.com, select “My flows” in the top navigation bar, and then select “+ Create from blank” to create a Flow from scratch.
• On the “Create a flow from blank”, select “Create from blank” to start the process of creating an empty Flow

Because this is a simple POC and it supposed to be a notification email we will be using the Scheduler trigger to start the Flow from time to time. For that you need:

• On the Flow designer, on the Trigger event enter “Schedule” and select the option “Schedule – Recurrence”

• For this POC, we want to receive a notification on a daily, so on the Schedule Trigger configuration set the:
  • “Interval” property as “1”
  • And the “Frequency” property has “Day”

• Add the next step by clicking the “+New step” button and then choose the “Add an action” option

• On the “Choose an action” window, enter “SQL Server” and select the action “SQL Server – Execute a SQL query”

Note: This can be a different action like, get rows or execute a stored procedure.

• After you connect to the proper SQL Server and database, on the Execute a SQL query action configuration:
  • On the “query” property, type

• Add the next step by clicking the “+New step” button and then choose the “Add an action” option
• On the “Choose an action” window, enter “Variables” and select the action “Variables – Initialize variable”

• On the Variable action configuration:
  • On the “Name” property, type “lines”
  • On the “Type” property, specify the type as “String”
  • On the “Value” property leave it blank

Note: Initialize variable needs to be performed on Top Level, in other words, outside any cycle operation (Do Until or For Each). Initialize variable inside cycles are not permitted.

• Add the next step by clicking the “+New step” button and then choose the “Add an action” option
• On the “Choose an action” window, enter “Variables” and select the action “Variables – Append to string variable”

• On the Variable action configuration:
  • On the “Name” property, select “lines” variable
  • On the “Value” property type

Note: @{items(‘Apply_to_each’)?[‘Name’]} and @{items(‘Apply_to_each’)?[‘Nacionality’]} are inserted and selected from the list of “Execute a SQL query” action tokens

• Because we can have multiple rows retrieved from SQL Server, the Flow designer will automatically place this “Variable – Append to string variable” inside a loop that will iterate thru each SQL row.

Now that we have our SQL rows partially well-formatted in a Markdown-formatted table, what we need to do is create our notification/Approval Request email. To do that we need:

• Add the next step by clicking the “+New step” button and then choose the “Add an action” option
• On the “Choose an action” window, enter “Office 365 Outlook” and select the action “Office 365 Outlook – Send an email”

• On the Office 365 Outlook action configuration:
  • On the “To” property, type the email for which you want to send the email
  • On the “Subject” property, type the subject of the email
  • On the “Body” property, type:

Microsoft Flow Execute Sql Query In Entity Framework

Note: @{variables(‘lines’)} is inserted and selected from the list of “Variables” tokens

Now the final trick for you to receive this type of email:

instead of this:

On the Office 365 Outlook action configuration you need to:

Microsoft Flow Execute Sql Query Tutorial

• Click on “Show advanced options”.

Microsoft Flow Execute A Sql Query Formal Parameters

• And on the “Show advanced options”:
  • On the “Is HTML” property, select “Yes”