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.
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.
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:
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:
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.
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.
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.
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.
14 thoughts on “SQL 4 CDS v7.1 Released”
I’m so impressed about what you have accomplished with SQL4CDS and you’re keeping pushing the boundaries.
Well done & thanks.
Nice one Mark
I literally use your tool every single day and have become so reliant on it. Top work!
Does this tool handle the full range of Transact-SQL like Outer Joins, Update > Set > From, SubString, PatIndex, DateTrunc, SubQueries, etc.?
Sorry, to clarify, I am asking about SQL 4 CDS.
It certainly implements a lot of it, it’s not 100% but if you come across a feature it doesn’t support that you have a need for, please open an issue on GitHub!
I want to execute an alter table. Is that possible with SQL 4 CDS?
No, DDL statements are not currently supported.
I love this tool. It would be great if can update the Managed property of the entity via this tool.
Currently, we can only select query against the Medata entity table
WHERE objecttypecode = 9940;
But the Update is not working, it’s throwing a message: Invalid schema name: metadata
All data tables are in the ‘dbo’ schema
SET iscustomizable = 1,
ismanaged = 0
WHERE objecttypecode = 9940;
i, I want to execute a sql concat function. Is that possible with SQL 4 CDS?
I haven’t implemented the CONCAT function, but you should be able to use the + operator instead
What an impressive tool, thank you for developing it.
I have a requirement to include counts as part of a selection but have noticed that the fetchxml generated omits the counts, is that because counts aren’t supported by fetchxml?
Counts are supported by FetchXML, but it may be that other parts of your query aren’t. Could you share your SQL please?
I love this tool. But unfortunately it’s not working on Mac.
I love this tool in xrmtoolbox. I was very happy when i found out that this is available for Azure Data Studio also. I can use direct sql connection using TDS in azure studio, but not via SQL4CDS. Looks like it depends on SQL4CDS Language Server, which is a windows executable (exe). I think to make it work on Mac, it should be a mac executable. Just wanted to check if there are any plans to fix it for Mac.
Getting following error on mac:
[Error – 8:46:41 AM] Starting client failed
Launching server using command /Users/xxxxxxx/.azuredatastudio-insiders/extensions/mark carrington.azuredatastudio-sql4cds-7.1.0/out/sql4cdstoolsservice/MarkMpn.Sql4Cds.LanguageServer.exe failed.
Thanks for letting me know. The intention was for it to work cross-platform, as the language server is built on .NET 7. It looks like I messed up the command to start it though, I’ll try to get that fixed for the next update. I don’t have a Mac to test it on unfortunately though.
I’ve added this as an issue to the GitHub at https://github.com/MarkMpn/Sql4Cds/issues/285