Know SQL and want to access your CDS/PowerApps/D365 data? Enter SQL 4 CDS, my XrmToolBox plugin to get a SQL Server Management Studio type interface to query your data!
Using standard SQL syntax, execute
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!
Connecting to CDS
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.
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.
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.
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.
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.
As well as querying your data with
SELECT queries, you can also use
DELETE queries to modify it.
Use the Settings button to control details of how queries are run by SQL 4 CDS.
When running a
SELECT statement without a
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
DELETE statement is about to affect more than a specific number of records. By default this is set to 0, so any
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.
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.