You’re offline. This is a read only version of the page.
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.
: )
Makes you really skilled at learning the intricacies of OData queries and fetchXML! (still short of SQL without joins on multiple columns, filters comparing column values across tables, HAVING clause, sub-queries/CTE, *ETC* ; )
Bump on this thread to promote core-function upgrade. There really should be a native means of using SQL on Dataverse data in flows. I often run into tasks where I could retrieve my target rows with a very simple SQL query, but instead takes an awful lot of work and looping to do the same thing with numerous List Rows and conditional checks. Feels sloppy when the potential for a simple solution is so very close.
@bullmau185 nothing on the certification yet, but we have placed more info online on Connect Bridge for Power Automate, including a step-by-step explanation for connecting WooCommerce to Dynamics NAV. I hope you find it useful.
Thank you, @bullmau185 and @ana_CS , for the updates. Props to rising to the challenge i proposed, i just never got back to this thread. For my own case, i found some suitable-enough workaround with fetchXML and just a large data set which i then filtered in a Power Automate step. It turns out We don't even need that particular flow anymore anyway.
@Spearruleina - Having played around with this proposed option, I can now confirm it does a good job at querying the data. I was actually able to implement the same query on the Customer Support Portal Dataverse data that @ana_CS mentioned. This allowed me to monitor live if a portal comment had been left in the "open" status for more than 30 minutes without the support clicking on the "Send" button so the customers would see the Portal Comment in their account and then I setup an email to the specific user to tell them they forgot to click Send. Pretty helpful since Dynamics is a pain to try to get data based off specific times e.g. "In the last 30 minutes".
I was also able to establish a connection with my on-premises Dynamics NAV 2017 with Connect Bridge and query data. From here, I can even build integrations using Power Automate.
However, I have also found that I can query the data directly in Power BI Desktop, so if you are just looking to query the data, I find that adhoc queries are best done using XrmToolBox while monitoring live data is best through Power BI Desktop.
But if you are looking use SQL to build Power Automate Flows and create integrations, Connect Bridge is absolutely a viable option.
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.
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.
@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?
@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.
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.
#1
stampcoin
17
#2
ankit_singhal
11
Super User 2025 Season 1
#3
mmbr1606
9
Super User 2025 Season 1
Share
Report
All responses (
Answers (
