Power Automate: SQL query to the Dataverse. How to connect?

(0) Share

Report

Posted on by Max77

264

Hi, guys!

There is a way to connect to the dataverse with SQL Management Studio

https://learn.microsoft.com/en-us/power-apps/developer/data-platform/dataverse-sql-query

You should choose Azure Active Direcory.

It works.

But how to use it from PowerAutomate?

The Authentication type List doesn't contain position "Azure Active Direcory".

"Azure AD integrated" doesn't work.

Who knows how to get data? Maybe another connector.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (15)

Answers (0)

AhmedSalih

6,678 Moderator on at

Like (0)

Report

Hello, @Max77, do you have the Dataverse tables in SQL Server? If not, just connect to the Dataverse tables through the Dataverse connector.

If my reply helped you, please give a 👍 , & if it solved your issue, please give a 👍 & Accept it as the Solution to help other community members find it more.

I am primarily available on weekdays from 6-10 PM CT and 5-10 PM CT on weekends.

Visit my Blog: www.powerplatformplace.com

Visit my YouTube Channel: https://www.youtube.com/@powerplatformplace/videos

Was this reply helpful? Yes No

Max77 264 on at

Like (1)

Report

@AhmedSalih , I don't have saparate SQL Server. I need to do a complicated SQL query to Dataverse.
It's a very cumbrous way to use "Dataverse List Rows" and compose data and it's too slow.
I'd like to do a direct query to the Dataverse.

Was this reply helpful? Yes No
AhmedSalih 6,678 Moderator on at

Like (0)

Report

@Max77, SQL Server Connector is to connector to Actual SQL server. You will need to have a SQL server with SQL Database, then create Virtual Entities in Dataverse.

Was this reply helpful? Yes No
Spearruleina 36 on at

Like (0)

Report

Max77, thank you for having the same question.... disappointed to find out this may not be possible. PowerAutomate might like some more Power behind it... Will continue to research ways of pushing as much querying and shaping of data to the server as possible, and shall let you know here if i find any good ways to ease this pain point.

Was this reply helpful? Yes No
ananeto 20 on at

Like (0)

Report

If all you need is to gather Dataverse data as if it were in relational tables or to move it to an SQL Server, I suggest having a look at this article Getting Started with Microsoft Dataverse Integrations.

If you need to involve Power Automate, we have a (paid) Power Automate Connect Bridge connector awaiting certification. This will come out soon, and I can give you details on what you could do with this if you reach out through https://www.connecting-software.com/connect-bridge-sign-up/.

Was this reply helpful? Yes No
Spearruleina 36 on at

Like (0)

Report

Thank you for the response. I perceive the links you posted as a little salesy, but if you can provide an example of SQL 'HAVING' clause in a connection to Dataverse, i would feel intrigued to learn more.

Was this reply helpful? Yes No
ananeto 20 on at

Like (1)

Report

@Spearruleina, here is an example

SELECT incident.ticketnumber, cast(adx_portalcomment.createdon as datetime) as createdon ,adx_portalcomment.createdby
FROM incident
JOIN adx_portalcomment ON incident.incidentid = adx_portalcomment.regardingobjectid
WHERE adx_portalcomment.statuscode = 1
AND adx_portalcomment.adx_portalcommentdirectioncode = 2
AND cast(adx_portalcomment.createdon as datetime) < '2023-09-07 11:30:30.000'
HAVING count (adx_portalcomment.regardingobjectid) > 1

I can also share with you the part of the flow we use for such an alert.
We are happy to have a call with you to go over what I did and how it works if you find it interesting for your use case.

Was this reply helpful? Yes No
AM-03091305-0 31 on at

Like (0)

Report

@ana_CS - You mentioned this solution https://www.connecting-software.com/connect-bridge/ is going to be out soon? Any update on the certification? Is it going to be a Premium app that is found in the Applications and then we need to buy a subscription from you for it to work? Will this work for Power Automate Desktop and Power Automate Cloud?

Was this reply helpful? Yes No
ananeto 20 on at

Like (1)

Report

Hello @bullmau185, unfortunately I still do not have a timeline on the certification. We are awaiting feedback from Microsoft.

Yes it will be a Premium app after the certification process. The application requires Connect Bridge to be installed locally on a machine of your choice inside, and a paid license is required for Connect Bridge to run. This you can order through the sales team and request a free 15-day trial.

Good news is that it already works with Power Automate Desktop without the need for the certified connector. The connector is only that it works with Power Automate Cloud, so if you have Power Automate Desktop, you can already trial it and setup some queries.

Was this reply helpful? Yes No
AM-03091305-0 31 on at

Like (1)

Report

Thank you @ana_CS - I have contacted your sales team to request a free trial. Anxious to give it a try and see how it works with Power Automate Desktop. Please ping me when the certification goes through and I will try the Cloud version as well.

Was this reply helpful? Yes No