Executing a query and sending it to email with Power Flow

[jeppevatane]

Hello all,

I'm new to Power tools and I need some help! I'm trying to create a flow which goes something like this: 

Step 1. Execute a query that finds new contracts from our database, query below

SELECT
t1.Name'
,t2.SocialSecurityNo'
,t2.emailaddress'
,t2.telephone'
,t1.value'
,t1.createdon'
,t1.contractnumber

FROM .[dbo].[contract] AS t1
LEFT JOIN[dbo].[pro_contact] AS t2 ON t1.id = t2.Id
WHERE  t1.createdon >= DATEADD(HOUR,-1,GETDATE())

AND t1.contractnumber = 29201019

ORDER BY createdon desc

Step 2: Send the results to a specified email

As it happens, I am running an on-premise gateway, so the 15 minutes I was planning on this project turned out to be much longer. Is there a way to do this?

KR,

jeppe

[ManishJain]

Hi @jeppevatane ,

Can you please share more on querying part. Are you trying to connect to OnPrem database and using it in Online CRM version ? 

If so please follow the below url to connect : 

https://www.c-sharpcorner.com/article/how-to-connect-on-premise-sql-server-from-microsoft-flow-using...

https://www.mssqltips.com/sqlservertip/4918/microsoft-flow-and-sql-server-integration-example/

Thanks

[jeppevatane]

Hi  @ManishJain and thank you for your links! We have a Dynamics-based CRM and therefore we have a OnPrem database where I extract the data from. I am trying to extract contracts that have been created within the last hour, which I will send to our partner automatically. This information will be extracted with SQL Management Studio from our crm database.

When I am trying to create the flow (Execute a Query), I get this error message "Gateway GetPassThroughNativeQueryMetadataAsync - this operation (Get Native Sql Metadata) is currently not supported using an on-prem gateway connection"

jeppe

[ManishJain]

Hi @jeppevatane ,

Thats great. If you follow the links for connecting to On Prem gateway and use Execute a Query action to retrieve data from database. 

In Order to send formatted email you can use following url :

https://spmaestro.com/handling-json-in-microsoft-flow/

Thanks

[jeppevatane]

Hi @ManishJain ,

When I am trying to create the flow (Execute a Query), I get this error message "Gateway GetPassThroughNativeQueryMetadataAsync - this operation (Get Native Sql Metadata) is currently not supported using an on-prem gateway connection". 

jeppe

[ManishJain]

Hi @jeppevatane ,

Please share your flow screenshot.

Thanks

[jeppevatane]

Hi @ManishJain 

Here is a picture of my flow, it immediately gives me the error message. It's in Finnish, but the SQL step is "Execute a Query".

[ManishJain]

Hi @jeppevatane ,

Even I got the same message. Please try to use Get rows(V2) action instead and specify your server name , database and table name. When you click on show advanced option you have other fields which you can use to further narrow down the data you want. 

Thanks

[jeppevatane]

Hi @ManishJain ,

But I can't join 2 tables in the "get rows"-step, can I?

jeppe

[ManishJain]

Hi @jeppevatane ,

With on-prem gateway currently it is not supported . Might be you can do two calls where in based on first call try to get data from second table.

Thanks

[ManishJain]

Hi @jeppevatane ,

Please follow this url : 

https://flow.microsoft.com/fr-fr/blog/powerquery-flow/

Thanks

[jeppevatane]

Hi @ManishJain ,

I don't think that works for me, as I need to check results every hour and only print new contracts that have been created within the last hour. How would I do this with power query?

Edit. I managed to get my power query working as wanted, but sending an html table to my email seems to be the next issue. I get the following error when trying to create an html table: "The 'from' property value in the 'table' action inputs is of type 'Object'. The value must be of type 'Array'."

jeppe

[ManishJain]

Hi @jeppevatane ,

Can you please share screenshot of table data output. Possibly you need to do compose first and see which item to be picked for loop.

Thanks

[jeppevatane]

Hi @ManishJain , 

I made it work! My flow goes as follows: 

1st step: Recurrence once a day

2nd step: We find two relevant tables from our SQL server, filter the created column to find contracts created today and finally merge the tables together, deleting irrelevant columns

3rd step: Select Data - we parse the result with the select data-step and name the selected values

4th step: we create a table from the output of our parsing

5th step: We send the result html table to a selected email address

Thanks @ManishJain  for your help!!

jeppe