I asked a little while ago what new features you’d most like, so here’s the first of them: cross-instance queries!
When you connect to multiple instances in the Object Explorer pane on the left you can use each of those instances in your query:
This is very useful for quickly comparing configuration data and identifying anything that’s out of sync across your dev/test/uat/prod environments. For example, in the screenshot above I’m checking for any solutions that are installed in dev but not in prod, or that have different versions:
SELECT dev.uniquename, dev.version, prod.version FROM [Dev Environment].dbo.solution AS dev LEFT OUTER JOIN [Prod Environment].dbo.solution AS prod ON dev.uniquename = prod.uniquename WHERE prod.solutionid IS NULL OR prod.version <> dev.version;
In the query you can refer to each environment by the name you registered it with in XrmToolBox. If the name includes spaces or special characters you’ll need to enclose it in square brackets in your SQL.
Data tables are in the
dbo schema, so the full name of a table in a different instance is
Remember SQL 4 CDS also allows you to query metadata, so this also allows you to check for metadata differences between instances. For example, you could find entities that are missing from your prod environment or where the display name is different using:
SELECT dev.logicalname, dev.displayname, prod.displayname FROM [Dev Environment].metadata.entity AS dev LEFT OUTER JOIN [Prod Environment].metadata.entity AS prod ON dev.logicalname = prod.logicalname WHERE prod.logicalname IS NULL OR prod.displayname <> dev.displayname;
Unfortunately this feature is currently only available in the XrmToolBox version of SQL 4 CDS, not the SSMS plugin.
It’s designed to compare relatively small data sets such as configuration entities or metadata. If you need to compare 10 million contact records this isn’t the ideal tool for you.
If you include primary key fields (like
account.accountid) or foreign key fields (like
account.primarycontactid) in your query, they will now appear as links in the grid view:
You can still click on each value to select the cell if you want to copy the value as normal, but you can now also double-click on it to open the record in your browser.
You can also right-click a link and select “Create SELECT Statement”. This will add a simple
SELECT * FROM <table> WHERE <primarykey> = '<value>' query so you can get more details of that record.
This release adds supports for more standard T-SQL functions:
USER_NAME() you can write queries to return data based on the logged in user so your query can automatically adapt to who is running it, e.g.
-- My accounts created in the last month SELECT name FROM account WHERE createdon > dateadd(month, -1, CURRENT_TIMESTAMP) AND ownerid = CURRENT_USER;
There’s a new button to covert your queries to use them in Power BI reports! This builds on a LinkedIn post by Henry Jammes and uses the Dataverse connector to run the query using the TDS endpoint.
This new button is available in both the XrmToolBox and SSMS versions of SQL 4 CDS. Click it to get the M query version of your SELECT statement ready to copy & paste into your report.
/* Query converted to M format by SQL 4 CDS To use in Power BI: 1. Click New Source 2. Click Blank Query 3. Click Advanced Editor 4. Copy & paste in this query */ let Source = CommonDataService.Database("contoso.crm.dynamics.com") DataverseSQL = Value.NativeQuery(Source, "SELECT name FROM account WHERE createdon > dateadd(month, -1, CURRENT_TIMESTAMP) AND ownerid = CURRENT_USER;", null, [EnableFolding=true]) in DataverseSQL