SQL 4 CDS is a XRMToolBox tool from Mark Carrington that will change the way you query data in Dynamics and can speed up query multiple environments. You can create scripts which you can quickly run in any environment.
When you are an experienced Dynamics 365 user the GUI can slow you down, when maintaining the system you want to view certain data that highlights problems.
The first method to view data with the GUI is using Advanced find, creating views — Why advanced find is an awesome tool in Dynamics 365 . You don’t need to see all the data, just the data you are interested in and filter out the rest.
Another recommended tool is the FetchXML Builder by Jonas Rapp, which works with the XRMToolbox . This allows you to query Dynamics data and query data not visible in advanced find. You can save and run queries from advanced find by saving the fetchxml and running it.
Dynamics 365 and SQL
Then whilst attending the Birmingham CRMUG virtual meeting we were talking about the endpoint added to Dynamics 365 to allow SQL queries with Dynamics 365. I wrote about it a few days before — Woohoo — You can query Dynamics 365 (CDS) with SQL….again. When Dynamics 365 was on-premise it was useful to query the database directly to see what was really happening and find problems. There are lots of on-premise developers who miss querying the Dynamics SQL database.
If you want to learn about the new CDS T-SQL endpoint , Mark Carrington creator of the SQL 4 CDS tool has done a step by step guide to setting it up.
- CDS T-SQL Endpoint pt 1 — Connecting
- CDS T-SQL endpoint pt 2 — First Thoughts
- CDS T-SQL endpoint pt 3 — SqlClient
- CDS T-SQL endpoint pt 4 — Performance
- CDS T-SQL endpoint pt 5 — EntityFramework / ORM
- CDS T-SQL Endpoint pt 6 — Aggregates
- CDS T-SQL Endpoint pt 7 — Extensibility
- CDS T-SQL Endpoint pt 8 — Security
CDS T-SQL endpoint stole some of thunder from the SQL 4 CDS but after the investigation Mark has enabled the T-SQL endpoint in SQL 4 CDS in this version SQL 4 CDS 2.1.0 — the T-SQL edition. Personally I’m quite happy to use CDS 4 SQL rather than use the SQL tools on my laptop.
SQL 4 CDS
SQL 4 CDS allows you to write these SQL statements agaisn’t a Dynamics 365 environment
Yes, you read that correctly, it allows you to delete, insert and update records as well as querying.
I have been using the SQL 4 CDS XRMToolbox tool more often and find it a great tool that allows me to get information from Dynamics faster than using the GUI.
SQL 4 CDS will need you to invest some time to create the queries, which was initially slower for me because I hadn’t written any SQL queries for many years.
What do I like
- You can connect to multiple environments in one tab
- autocomplete for fields
- Its easy and quick to use
- You can save queries and load them in seconds
- It can save the queries into FetchXML, so you can use them in plugin queries, integrations or the FetchXml builder
- Create, update and delete records
- You can open records
What have I used it for
Querying Dynamics 365
It took me a while to refresh myself with writing SQL, not something I have done for 15 years.
The common reason I use the CDS 4 SQL is to query data. It’s fast and easy to use. I can use the query agaisnt multiple environments, which is useful for comparing environments and finding problems.
Creating queries is fast and the autocomplete makes it easy
The tool is great for exporting data and creating files for exporting and importing in different environments. You can select the fields, create a filter and then copy the data and headings to an excel file.
Here is a query I used to query for teams
select teamid, name, description, teamtype, businessunitid, azureactivedirectoryobjectid
where teamtype = 2
This query selects where teams which are AAD teams, this is where you create an AD group and link to a team in Dynamics, when you add a user to the team it automatically adds those users to the teams in Dynamics.
I use it to create date for the my release, so I can keep the guids synchronised between environments and avoid the cost of manual deployments activities
Draft workflows and draft business rules
After I do a release to an environment, I like to check if the there are any draft workflows or business rules because sometimes data is missing in the target environment and sometimes I need to manually activate the workflows/Business rules on the first import into an environment.
FROM workflow AS w
solution AS s
ON w.solutionid = s.solutionid AND (s.friendlyname like ‘%Hosk%’)
WHERE w.type = ‘1’
AND (w.category = ‘0’ — category 0 = workflow
OR w.category = ‘3’) — category 3 = action
AND w.statecode = ‘0’ — statecode 0 = draft
This query has a couple of cool things.
- You can join tables to get more information.
- The join is here is with the solution table and filter by solutions whats in the text e.g. Hosk
- it looks for Workflows and actions in a draft state
- you can add comments on a line by adding —
You can quickly query the solution versions where the name is like something, usually you name your solutions. The reason I have to use friendly name is because the solutions have different guids in different environments and I want these queries to be run on different environments.
select friendlyname, version from solution
where friendlyname like ‘%solutionname%’
If you want examples of more complex queries, there are some on this page below
One limitation I have found is when you create a record you can’t specify the guid of that record. This is fine for contacts and records to use but for any records you want to reference from workflows/plugins/flows then you want to keep the guids the same.
SQL 4 CDS is fast and easy to use and you can run one query on multiple environments in seconds. I am starting to build up more queries which I run regularly after releases (hopefully I can make this into PowerShell scripts at some point).
Add the SQL 4 CDS and give it a go and you will soon find yourself using it more and more, particularly if you query multiple environments.
Other interesting articles