I asked a little while ago what new features you’d most like, so here’s the first of them: cross-instance queries!
Joins between Dataverse instances
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.
Links in result grid
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.
More supported functions
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;
Converting to Power BI
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
20 thoughts on “SQL 4 CDS 5.3 Released”
Downloaded version 22.214.171.124 and having trouble inserting Guid values in tables. Receiving the following error:
“No implicit type conversion from System.Data.SqlTypes.SqlString to MarkMpn.Sql4Cds.Engine.SqlEntityReference: ”
Here is a sample insert statement:
insert into tbl1 (id, guidColumn) values (‘5′, ’29b5a20d-5a65-eb11-a812-001dd801e96f’)
This used to work in prior versions.
Do I need to change the syntax on my end, if so, how? Any guidance would be appreciated.
This should be fixed in 5.3.1, let me know if you still have the problem after updating!
I suppose you haven’t seen my last comment for version 5.2 yet, so I allow me to post it here again, as the error is still present on version 5.3:
I’ve just realized that current version does not support audit queries anymore. If I take your example “Audit aggregates” from above or any other simple way to query audit, I always get this error:
RetriveMultiple call on Audit Entity must specify exactly one Condition set on objecttypecode at the top level filter
See the Execution Plan tab for details of where this error occurred
This occurs even with queries I’ve successfully executed with former versions of SQL4CDS
Strange, I can still run that same query without error, and the equivalent FetchXML also runs in FetchXML Builder:
Do you have some other example I can use to reproduce the problem?
I have the same error on FetchXML, so I suppose it has something to do with the instance I am connecting to. I will try to reach out if there has been a change regarding configuration or rights, though I’m wondering if this error message can be caused by such issues.
it’s a little bit strange. The error occurs, if table audit is on entity tag in fetch.xml.
If I switch it, so that systemuser is on entity tag and audit is link-entity, it is working without error.
Older versions of SQL4CDS (e.g. version 4.1) but audit on link-entity, whereas now it is put on entity-tag.
This is the FetchXML that’s generated from the example query I posted. Can you post the exact SQL you’re running to get it to generate the failing FetchXML please?
I’m on 5.3.1 but I’m not getting the links in my grid view.
Can you share the query you’re running please?
WHERE accountidname IS NOT NULL;
I don’t have the
mca_fields on my instance, but if I use
parentcustomeridnameinstead I see
parentcustomeridshown as a link as expected. Can you show me a screenshot of what you see when you run this query?
Any tips on how to post a screenshot here?
Best way would be to create an issue on GitHub https://github.com/MarkMpn/Sql4Cds/issues/new
I’ve just realized version 5.3 shows dates no longer in format configured on windows but always as yyyy-mm-dd hh:mm:ss.nnn
This causes my some headache, as when I copy data to excel it is not recognized as date anymore.
Is there any option to return to old behaviour?
This format is consistent with the one used by SSMS and avoids any ambiguity, hence the change. When I copy & paste a value in that format into Excel it recognises it as a date, but only shows the time part by default. Switching the cell format gets it to show the other parts as required.
I’m sorry, but this does not work for an excel running on windows with german date/time format settings.
When I copy & paste, Excel treats it as a simple string, shows it in full length and applying any data format has no effect, as excel does not recognize it as a date.
OK, I’ll include an option for this in the next release.
Thank you very much
Hi Mark. I am getting this error when I attempt to do an update:
Method not found: ‘Void Microsoft.Xrm.Tooling.Connector.CrmServiceClient.set_EnableAffinityCookie(Boolean)’.
I am sure it has to do with am out of date DLL on my box. I have updated the GAC version of my Xrm DLLs and also have the latest version of XRMToolBox. Can you provide any additional insight?
Yes, definitely an out of date copy of that dll. Unless you’ve got a particular reason for having it in the GAC I’d suggest removing it from there so XrmToolBox will always load its local version.