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 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.
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.
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.
Double-click on the connection to view the dashboard. This shows information about the database and the objects in it.
Click the New Query button and enter your SQL query as normal. Press F5 or click the Run button to execute it.
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.
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.
As well as these new integrations I’ve also added a lot of usability enhancements to the XrmToolBox tool as well.
Press Ctrl+F to open the Find bar, or Ctrl+H to open the Replace bar:
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.
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:
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.
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.
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.
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.
Once you’ve saved a query, you can now use the new Save As option to save another copy as a different file.
As well as all these new integrations and usability improvements, the core engine has also received some important updates.
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:
- Update it to Draft state as the record is read-only in Lost state
- Update the Description field
- Update it to Active state
- Use the WinQuote message to change the state to Won
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.