Know SQL and want to access your Dataverse/CDS/PowerApps/D365 data? Enter SQL 4 CDS, my XrmToolBox plugin to get a SQL Server Management Studio type interface to query your data! If you’re using SSMS to connect to the TDS endpoint you can also check out the SSMS edition!

Introduction

Using standard SQL syntax, execute SELECT, UPDATE, INSERT and DELETE statements against your CDS instance.

But wait! I thought you couldn’t run SQL against online instances, and wasn’t supported for on-premise?

Don’t worry, SQL 4 CDS translates each query to supported FetchXML before it executes the query.

As well as executing the query directly, you can also choose to view the corresponding FetchXML as a quick way of writing FetchXML queries using your existing SQL knowledge and reuse them elsewhere.

SQL 4 CDS is being used daily by users wanting to investigate and manage their CDS data – check out the latest report showing how it’s being used!

Installation

If you don’t have XrmToolBox already, download that first. It’s free and includes a whole bunch of tools that will make your life easier.

In XrmToolBox, click Configuration > Tool Library. You’ll see list of all the tools you can use in XrmToolBox. Scroll down to SQL 4 CDS and tick it, then click the Install button in the toolbar.

Now you can return to the main XrmToolBox screen. Under the Tools tab you can now search for SQL 4 CDS and click on it to open it.

Connecting to Dataverse/D365

When you open SQL 4 CDS, XrmToolBox will prompt you to connect to an instance, and the name of the instance you’ve connected to will be shown in the Object Explorer pane on the left of SQL 4 CDS.

You can connect to more instances by clicking on the Connect button in the toolbar. Select a connection and click Connect, and the new connection will be added to the Object Explorer pane.

Browsing Metadata

Expand the connection entry in the Object Explorer pane to view the metadata for that instance. See the list of entities, and expand an entity to see a list of attributes and relationships within the entity.

You can also query metadata with SQL using the entity, attribute, label, globaloptionset, relationship_1_n and relationship_n_n tables.

Building Queries

When you first open SQL 4 CDS a new query tab will be created. You can enter your SQL query here to start querying the CDS instance you are connected to.

You can create new query tabs by clicking the New Query button in the toolbar. Each query tab is linked to one CDS instance – the name of the instance used for each query is shown in the name of the tab. Select an instance in the Object Explorer pane before clicking New Query and that instance will be used for the new query tab.

Attributes and joins can be added to your query by double-clicking on them in the Object Explorer. Double-click an attribute to add its name into your script, or a relationship to add the corresponding join details.

The SQL query can be tidied up to a standard layout using the Format button. This standardises the casing, spacing and other syntax details while keeping the meaning of your query the same.

Running Queries

Once you’ve written your SQL query, click the Execute button in the toolbar or press F5 to run it. If you have some text selected in your query, only that part of the query will be run. Otherwise, the entire query script will be run. A script can contain more than one query statement, and each one will be run in turn.

The results of each query will be shown at the bottom of the query tab. SELECT queries will show a table of the results, and other queries will show the number of affected rows. If an error was encountered with the query, that error is shown instead.

An extra FetchXML tab will also be shown with the results for each query. This shows the FetchXML that the SQL query was translated into for execution. From this tab you can send the query to FetchXML Builder if you want to edit it further, or translate it to other formats such as OData.

The FetchXML can also be retrieved by clicking the button next to the Execute button. The selected query will be converted to FetchXML but will not be executed.

Some queries can’t be run with FetchXML alone. For those queries you’ll get a warning indicating that the query will take some additional processing within SQL 4 CDS to get you your results.

Modifying Data

As well as querying your data with SELECT queries, you can also use INSERT, UPDATE and DELETE queries to modify it.

If you’re running queries to update or delete a lot of records, it’s more efficient to write a single query like:

-- Deactivate any contacts without a first name
UPDATE contact
SET    statecode  = 1,
       statuscode = 2
WHERE  firstname IS NULL;

rather than work out the IDs of the records you want to update yourself and then write lots of queries like:

UPDATE contact SET statecode = 1, statuscode = 2 WHERE contactid = '<guid1>';
UPDATE contact SET statecode = 1, statuscode = 2 WHERE contactid = '<guid2>';
UPDATE contact SET statecode = 1, statuscode = 2 WHERE contactid = '<guid3>';
UPDATE contact SET statecode = 1, statuscode = 2 WHERE contactid = '<guid4>';
UPDATE contact SET statecode = 1, statuscode = 2 WHERE contactid = '<guid5>';

Intellisense

SQL 4 CDS includes Intellisense to help you write your queries accurately. As you type you will see suggestions of available table, column and function names that you can include.

You can also hover over the name of a table or column to get a description of that item.

All this information is loaded from your Dataverse/Dynamics CRM metadata so it will automatically include all your customizations too.

Impersonation

If you have the required permissions, you can run your queries as another user. This lets you see the results of a SELECT query based on the access rights of the other user, or make sure the createdby or modifiedby fields are set correctly when changing records.

You can either use the username drop-down in the status bar at the bottom of the screen to select a different user:

or you can use SQL such as:

EXECUTE AS USER = 'other-user@contoso.com';

-- Get the number of accounts the other user has access to
SELECT count(*) from account;

REVERT;

Additional Settings

Use the Settings button to control details of how queries are run by SQL 4 CDS.

Limit Results

When running a SELECT statement without a TOP or OFFSET clause, SQL 4 CDS will retrieve all the matching records. Although CDS only returns the first 5,000 records, SQL 4 CDS will retrieve all the additional pages of results as well. Use this setting to ensure only a limited number of results will ever be retrieved to avoid accidentally retrieving very large data sets.

Safe Updates and Deletes

Use these settings to generate a warning message when an UPDATE or DELETE statement is about to affect more than a specific number of records. By default this is set to 0, so any UPDATE or DELETE will generate a warning.

You can also avoid accidentally updating or deleting all your data by enabling the Prevent UPDATE/DELETE without WHERE options.

Bulk Deletes

If you are doing DELETE queries affecting a large number of records, you may want to enable the “Use bulk delete operations” setting. SQL 4 CDS will then start a background bulk delete job rather than deleting each record itself.