SQL 4 CDS v7.1 Released

Along with some important updates to the existing SQL 4 CDS suite of tools, this release brings brand new integrations for SSMS 19 and Azure Data Studio!

Azure Data Studio

Azure Data Studio is becoming the preferred tool for working with SQL Server databases, but recent updates have also brought extensions to work with other databases such as PostgreSQL and MySQL as well as KQL queries. Although the Dataverse TDS Endpoint gives compatibility with SQL Server Management Studio it still doesn’t work with Azure Data Studio. Enter SQL 4 CDS!

As with the existing XrmToolBox and SSMS tools, the SQL 4 CDS extension for Azure Data Studio allows you to connect to both online and on-prem versions of Dynamics CRM/365/Dataverse to query and modify your data using standard SQL syntax.

⚠ One prerequisite for this extension is .NET 7 – please download & install it before trying to install the SQL 4 CDS extension.

To install the extension, go to the latest release on GitHub and download the azuredatastudio-sql4cds-<version>.vsix. In Azure Data Studio click File > Install Extension from VSIX Package and select the file you’ve downloaded.

Connecting

With the extension installed you can now connect to your instance. Click the New Connection option and change the “Connection Type” to “SQL 4 CDS”. You now need to enter the details of your connection:

• URL: For online instances enter the main URL of the instance, e.g. https://org1234.crm5.dynamics.com/. For on-prem instances enter the full URL to the organization service, e.g. https://crm.contoso.internal/XRMServices/2011/Organization.svc. The on-prem connection uses the Data8 connection library which supports both IFD and Windows authentication, but does not support http connections – all connections must be https.
• Auth Type: choose from one of the following options:
  • Azure Authentication (Online): log in to an online instance using your standard user credentials
  • Username & Password (IFD): log in to an on-prem instance configured as an IFD with a username & password
  • Windows (On-Prem): use your current Windows credentials to log into a on-prem instance not configured as an IFD
  • Server-to-Server (Online): log in to an online instance using an application user
• Account: when using the Azure Authentication option, select the Azure AD account to authenticate as
• Azure AD Tenant: when using the Azure Authentication option, select the Azure AD tenant which contains the user
• Username & Password: when using the Username & Password authentication option, enter the username & password to use
• Database: leave as default
• Server Group: if you want to group your connections together, select a name here. This has no impact on the connection itself
• Name: if you want to give the connection a friendly name (e.g. “Dev”, “UAT”, “Prod”), enter the name here
• Advanced: when using the Server-to-Server authentication option, enter the details of the application user to authenticate with

Once you have entered the required details, click Connect. It can take several seconds for the connection to be ready.

Browsing the Object Explorer

The Object Explorer pane on the left hand side shows a summary of the available objects within the connection. Use this to explore the tables and columns in your instance, as well as the metadata tables you can use to get more information. Under “Programmability” you can also find the specialized messages you can call as if they were stored procedures or table valued functions.

Viewing information in the dashboard

Double-click on the connection to view the dashboard. This shows information about the database and the objects in it.

Running a query

Click the New Query button and enter your SQL query as normal. Press F5 or click the Run button to execute it.

Execution plan & FetchXML conversion

You can view the execution plan for a query using Ctrl+L, or press F1 to enter the command palette and start typing “estimated execution plan”.

Click on a “FetchXML Query” node in the plan to view the FetchXML query it uses. Click the “Show Query Plan XML” button on the right to open the FetchXML in a new tab.

You can also use Ctrl+M or the “Execution Plan: Enable/Disable Actual Execution Plan” to get the execution plan details when running a query. With this option enabled you can also view the time spent in each stage of the query to identify possible performance issues.

As well as the graphical Query Plan view, you can also use the Plan Tree to explore the logical structure of the query plan, and the Top Operations view to quickly identify the slowest points of the query.

SQL Server Management Studio 19

Microsoft have recently brought SSMS 19 out of preview. This has got a number of changes behind the scenes which means it needs a separate version of the SQL 4 CDS plugin. You can have both version 18 and 19 installed side by side.

The Releases page now has two SSMS installer files to download, one for version 18 and one for 19 – please make sure you download the correct version.

XrmToolBox

As well as these new integrations I’ve also added a lot of usability enhancements to the XrmToolBox tool as well.

Find & Replace

Press Ctrl+F to open the Find bar, or Ctrl+H to open the Replace bar:

Commenting

Use Ctrl+K followed by Ctrl+C to comment out the selected lines of a query, or Ctrl+K followed by Ctrl+U to uncomment them.

Autocomplete

The autocomplete suggestions now include possible values for picklist columns, such as when you’re writing a query like:

It also offers possible column names when you’re writing an INSERT query:

FetchXML to SQL conversion

If you have a FetchXML query in FetchXML Builder you can send it to SQL 4 CDS using View > SQL and clicking “Edit in SQL 4 CDS”. There are various different ways SQL 4 CDS can handle the conversion, and these have now been extended some more. Use Settings > Conversion to view the possible options, then click Advanced to get to the new options.

Environment Highlighting

Highlighting different environments in different colors is a very useful safety feature in XrmToolBox, helping remind you whether you’re in a “safe” development environment or a riskier production environment.

Because SQL 4 CDS lets you connect to multiple environments within the same tool, this highlighting hasn’t worked well in the past. This update brings highlighting to the individual query tabs so you can see more easily which environment you’re about to run each query in.

Tab menus

When you’re working with lots of different queries you can end up getting overwhelmed with the number of tabs you have open. This update brings a right-click menu to the query tabs that lets you quickly close ones you no longer need.

File change notifications

If you’ve got a saved query and edit it in a different program, when you switch back to SQL 4 CDS you’ll now get a notification that you need to reload the file.

Save As option

Once you’ve saved a query, you can now use the new Save As option to save another copy as a different file.

Query Execution

As well as all these new integrations and usability improvements, the core engine has also received some important updates.

State transitions

You might have seen a couple of posts from me lately about the restrictions on state transitions of various entities and when they are read-only. The latest versions of SQL 4 CDS now understands these automatically, so you can write a query like:

UPDATE quote
SET    statecode = 2, -- Won
       description = 'Updated'
WHERE  quoteid = 'guid'

It will check the existing state of each record to be updated and generate the required sequence of updates to get the desired effect. For example, if a quote is currently Lost and you run this query on it, SQL 4 CDS will:

1. Update it to Draft state as the record is read-only in Lost state
2. Update the Description field
3. Update it to Active state
4. Use the WinQuote message to change the state to Won

FORMAT function

You can now use the standard T-SQL function FORMAT to convert values such as numbers and dates to strings in specific formats, e.g.:

SELECT FORMAT(createdon, 'dd MMM yyyy')
FROM   account

I really hope you find these improvements helpful. Please continue to let me know any additional changes you would like to make SQL 4 CDS as helpful as possible.

This is only the first release of the Azure Data Studio extension so I’m sure there will be plenty of scope for additional enhancements – please add any issues to GitHub to let me know where it can be improved!

If SQL 4 CDS has been helpful for you, please consider leaving a review on the XrmToolBox tool site, and if it’s helped your company please consider a corporate donation to help support future development.