Features

SQL 4 CDS Features in XrmToolBox

Execute Queries

The "Execute" button runs the current query. If there is any selected text in the query editor, only that portion of the query runs. Otherwise, all the text in the query editor will run.

Execution Plans

You can obtain execution plans using the "Display Estimated Execution Plan" and "Include Actual Execution Plan" options. The execution plan will be shown in a new "SQL 4 CDS Execution Plan" pane. You can select nodes in the execution plan and see its details in the Properties pane.

You can also double-click a "FetchXML Scan" node to open the corresponding FetchXML query in a new tab.

Convert to FetchXML

Click the "Convert" > "FetchXML" button in the toolbar to convert the current SQL query to FetchXML. If the query requires more than one FetchXML query, multiple tabs will be opened. This is equivalent to opening the estimated execution plan and double-clicking on all the FetchXML Scan nodes.

Convert to SQL

From the FetchXML Builder tool, open or build your FetchXML query, click "View" > "SQL Query" and then "Edit in SQL 4 CDS".

Convert to M

Click the "Convert" > "M" button in the toolbar to convert the current SQL query to M format, ready to use in Power BI.

Switch Connections

Use the drop-down menu in the toolbar to select a different connection to link the current query tab to. All connections that have already been added to the Object Explorer pane will be listed here, as well as the option to pick a new connection. All connections are managed by the standard XrmToolBox connection list.

Connection Status

The yellow bar at the bottom of each query tab shows summary information about the connection:

• Current status
• Server name
• Username
• Organization name
• Elapsed time
• Row count

Click on the username field to impersonate another user or revert to the original user. Alternatively, use the EXECUTE AS/REVERT query syntax.

Format Query

Click the "Convert" button in the toolbar to apply standard formatting to the current query tab.

Browse Metadata

The Object Explorer pane displays details of the tables that are available in each instance. These are split into two sections, "Entities" and "Metadata". Within each table you can browse the list of attributes (columns) and relationships. Double-click on any to add them to your query.

Manage TDS Endpoint

For online instances, a "TDS Endpoint" item will be shown within the Object Explorer. Right-click on this for options to enable or disable the TDS Endpoint feature.

SQL 4 CDS Features in SSMS

Execute Queries

The SQL 4 CDS plugin will automatically detect when you are running a query that is for a D365 instance and is not already supported by the TDS Endpoint. Queries for regular SQL Server instances, and queries that only use supported Dataverse SQL, will continue to run without any interference by SQL 4 CDS.

When SQL 4 CDS detects an appropriate query that it can run, you will get the results in the grid and message panes as normal. The message pane will also show a header indicating it has used SQL 4 CDS.

If you do find SQL 4 CDS is incorrectly intercepting a query you do not want it to work with, include the phrase Bypass SQL 4 CDS in your query. This can be in a comment, e.g.

-- Bypass SQL 4 CDS
SELECT name FROM account

Execution Plans

You can obtain execution plans using the standard "Display Estimated Execution Plan" and "Include Actual Execution Plan" options. The execution plan will be shown in a new "SQL 4 CDS Execution Plan" pane. You can select nodes in the execution plan and see its details in the Properties pane in the same way as for a regular SQL Server execution plan.

You can also double-click a "FetchXML Scan" node to open the corresponding FetchXML query in a new tab.

Convert to FetchXML

Click the "FetchXML" button in the toolbar to convert the current SQL query to FetchXML. If the query requires more than one FetchXML query, multiple tabs will be opened. This is equivalent to opening the estimated execution plan and double-clicking on all the FetchXML Scan nodes.

Convert to SQL

With an XML file open and a TDS Endpoint instance selected in the Object Explorer, click the "SQL" button in the toolbar to convert the query to SQL.

Convert to M

Click the "M" button in the toolbar to convert the current SQL query to M format, ready to use in Power BI.