Woohoo - You can query Dynamics 365 (CDS) with SQL….again

When was the last time you heard anyone getting excited about SQL? #HoskWisdom

It’s a crazy world when Microsoft announces NEW functionality of being able to Use SQL to query data in CDS and the Dynamics/Power Platform community get excited about it.

Some things go together to make something beautiful,

Strawberries and cream
Chaz and Dave
Nerds and computers

Now after a lengthy absence we have SQL and Dynamics 365, back together

I was trying to think back when I wrote my last SQL statement, I’m guessing it was with on-premise Dynamics project in 2013. Before then I remember spending 2 days trying to format an SSRS report.

This made me realise there was more to life than SSRS reports

What is SQL back?

Microsoft announced that there is an SQL data connection available to the Common Data Service. This connection provides read only access to entities.

Be aware is a preview, soit might break and there might be bugs.

Read more here — Use SQL to query data (Preview)

You can query CDS with SQL Server Management Studio. Wow, I haven’t used that beast for a while.

Other interesting points

The security is based on the Common Data Service logins.

Supported operations

Filtering
Unions and Joins
Count & Max
Select
Batch operations

What won’t work

Updates
Inserts
Deletes

Anything that modifies data will not be allowed.

Non SQL data types won’t work (makes sense)

Binary
image
virtual
file
xml

FetchXML no more

FetchXML can be a pain but I was surprised by the positive support for the ability to integrate CDS with SQL queries. SQL is good but writing SQL queries doesn’t bring joy to my heart.

Why didn’t Microsoft create this or an SQL emulator years ago when Dynamics 365 went online, this would have made it easier for people to transition to Dynamics online, particularly the report writers.

SQL is more powerful than FetchXML and its easier to join tables. It’s useful to query the database to diagnose problems.

I don’t worry about what’s in the Dynamics database much anymore, I use it as a service and let Microsoft deal with it. The disadvantage of letting Microsoft sort out database issues, is after raising a support ticket it can take Microsoft a while to resolve the issue.

The last few premier support tickets I have raised have been resolved quickly and the engineers knew their stuff (but that’s not common in my experience of raising Microsoft support tickets).

SQL 4 CDS tool

I attended D365UG Birmingham virtual meetup and during the a talk from Matt Beard and he mentioned SQL 4 CDS an XRMToolBox plugin which gives you a SQL server management studio type interface created by Mark Carrington

I installed the app on my XRMtoolbox and its easy to use and has a nice autocomplete functionality.

It shows you the converted FetchXML statement from your SQL query and it allows you to delete data from Dynamics 365!

I encourage you to download and try it, rarely have I seen an XRM plugin that is so easy to use. It’s useful to have a tool which can query data in Dynamics without using the advanced find because Microsoft hides some of the entities and fields.

Replicate Dynamics to Azure SQL

An alternative to write SQL queries with Dynamics 365 is to replicate your Dynamics 365 database to an Azure SQL database using the Data Export Service. I discuss this in the blog post below

Dynamics 365 and replicating to an SQL database for reporting