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 instancename.dbo.tablename.

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.

More supported functions

This release adds supports for more standard T-SQL functions:

With CURRENT_USER or 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”

  1. Mark,

    Downloaded version 5.3.0.0 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.

    Thanks!!

    Rudy

    Reply

  2. Hi Mark,
    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

    Regards
    Olaf

    Reply

    1. Strange, I can still run that same query without error, and the equivalent FetchXML also runs in FetchXML Builder:

      <fetch>
  <entity name="audit">
    <attribute name="createdon" />
    <link-entity name="systemuser" to="objectid" from="systemuserid" alias="su" link-type="inner">
      <attribute name="fullname" />
      <attribute name="domainname" />
      <attribute name="userlicensetype" />
      <attribute name="accessmode" />
    </link-entity>
    <filter>
      <condition attribute="operation" operator="eq" value="4" />
    </filter>
  </entity>
</fetch>

      Do you have some other example I can use to reproduce the problem?

      Reply

      1. Hi Mark,

        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.

        Regards
        Olaf

        Reply

      2. Hi Mark,

        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.

        Regards
        Olaf

        Reply

      1. SELECT accountid,
        accountidname,
        mca_billingaccount,
        mca_billingaccountname,
        mca_shippingaccount,
        mca_shippingaccountname
        FROM contact
        WHERE accountidname IS NOT NULL;

        Reply

        1. I don’t have the mca_ fields on my instance, but if I use parentcustomerid and parentcustomeridname instead I see parentcustomerid shown as a link as expected. Can you show me a screenshot of what you see when you run this query?

          Reply

  4. Hi Mark,

    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?

    Regards
    Olaf

    Reply

    1. 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.

      Reply

      1. 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.

        Reply

  5. 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?

    Reply

    1. 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.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.