Dynamics 365 tool you should use — SQL 4 CDS

Image for post
Image for post

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.

Data

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 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 is an XRMToolbox plugin and you can run it from the XRMToolbox and don’t have to open Dynamics 365. The source code is in Github, so if you want to read or contribute you can — here

SQL 4 CDS allows you to write these SQL statements agaisn’t a Dynamics 365 environment

- SELECT
- INSERT
- UPDATE
- DELETE

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

Exporting data

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

from team

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.

SELECT w.name,
w.workflowid,
w.activeworkflowid,
w.statecode,
w.category,
w.primaryentity,
w.type,
w.ownerid,
s.friendlyname
FROM workflow AS w
INNER JOIN
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 —

Solution versions

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

D365UG UK: Data Integrity & Quality — Tips & Tools

Limitations

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.

Conclusion

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

Written by

Have been working with Dynamics 365 since version 4 and enjoy reading and delivering enterprise projects

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store