SQL 4 CDS – SSMS Edition

Now you can connect directly to your Dataverse database using SQL Server Management Studio you can do all the SELECT queries you want to get answers out of your data.

SQL 4 CDS also integrates with SSMS to give you even more power, with the option to run INSERT, UPDATE and DELETE commands as well as translate between SQL and FetchXML.

If you don’t have the TDS endpoint enabled or you’re using an on-premise version of Dynamics, please see the XrmToolBox version of SQL 4 CDS instead.

Installing

Download and run the MarkMpn.Sql4Cds.SSMS.Setup.msi installer from the GitHub releases page. If SSMS is already running you’ll need to restart it for SQL 4 CDS to be loaded.

You may receive a SmartScreen page like:

If you receive this warning, click “More info” then “Run anyway”.

Changing data

When you’re connected to a Dataverse database, SQL 4 CDS automatically handles running any INSERT, UPDATE or DELETE commands. Run them in the same way as normal by clicking the Run button or pressing F5 and SQL 4 CDS will do the rest.

Converting SQL to FetchXML

If you need to get a query in FetchXML format to use it with another tool or integrate it with a plugin, click the FetchXML button in the toolbar. If you can’t see it you might need to right-click on the toolbar area and select the SQL 4 CDS toolbar first.

Converting FetchXML to SQL

Open an XML file and make sure a Dataverse instance is selected in the Object Explorer pane. The XML button will then be enabled in the toolbar – click it to convert the FetchXML to a new SQL query.

Options

Under Tools > Options > SQL 4 CDS you can find a number of settings to control how SQL 4 CDS executes queries:

• Performance settings control how the create/update/delete record requests are sent to Dataverse. If you experience timeout errors when executing your queries you can try reducing these to send fewer requests in each batch and/or fewer batches at the same time to reduce the load on the server. This is especially useful if your queries trigger synchronous plugins.
• Safety settings will stop a potentially destructive query running accidentally across all your records. With these options enabled you won’t be able to run a query like DELETE FROM account when you meant DELETE FROM account WHERE name = 'Test'