This is mainly a bug fix release based on reports from users (thank you!) and telemetry. If you’ve hit problems running queries with SQL 4 CDS v5, please update to v5.1 and try again. If your query still doesn’t work, please let me know! You can put details of any errors you encounter in the comments at the end of this post, create a GitHub issue or send me a message on Twitter, LinkedIn or email sql4cds [at] markcarrington.dev

Virtual Attributes

One particular source of bugs were virtual attributes. These are the additional ___name and ___type attributes that give extra information about lookup and picklist fields.

The names for these attributes in the metadata are not always consistent. Although they normally use the “name” and “type” suffixes they are sometimes different, e.g. donotsendmm has an associated donotsendmarketingmaterialname attribute.

This becomes too difficult to keep track of when building & executing the query, so this update ignores the metadata for virtual attributes and uses its own consistent naming convention.

All lookup and picklist fields now have an associated column with a “name” suffix to give the formatted value. Any polymorphic lookup field also has an associated “type” field to give the logical name of the related record.

When you insert or update a polymorphic lookup field, you must specify both the id and type field.

If you used previous versions of SQL 4 CDS and used the CREATELOOKUP function to generate values to insert into lookup fields, e.g:

INSERT  INTO contact (firstname, lastname, parentcustomerid)
VALUES              ('Mark', 'Carrington', CREATELOOKUP('account', 'fd899ed7-7062-4679-86ad-689b91624cda'))

you can now do this by setting the values for the associated type field:

INSERT  INTO contact (firstname, lastname, parentcustomerid, parentcustomeridtype)
VALUES              ('Mark', 'Carrington', 'fd899ed7-7062-4679-86ad-689b91624cda', 'account')

This brings the XrmToolBox version into line with the SSMS plugin version.

Maximum Retrieval Limit

For some queries you might encounter the error:

Hit maximum retrieval limit. This limit is in place to protect against excessive API requests. Try restricting the data to retrieve with WHERE clauses or eliminating subqueries.
Your limit of 100 retrievals per query can be modified in Settings.

This is a new limit introduced in v5 designed to prevent you accidentally hitting your API request limit for complex queries, especially those involving subqueries. However, even in simple queries you can still hit this limit if you have a large number of records. Each retrieval gets up to 5,000 records, so with the default limit of 100 you’ll see this error on queries that need to retrieve over 500K records.

If you do need to increase this limit you can change it in the Settings screen below:

32 thoughts on “SQL 4 CDS 5.1 Released”

  1. Hello Mark,

    On Settings I’m not longer showing option to toggle results to GUID or Names. Older version had:
    Show lookup & picklist fields.

    Thanks
    Luis

    Reply

    1. You can now choose to get the guid, name or both by using the virtual fields, so this option no longer applied. For example, before you could write:

      SELECT parentcustomerid FROM contact

      and the option would determine whether you saw the guid or the name. Now you can write:

      SELECT parentcustomerid, parentcustomeridname FROM contact

      and get both versions at the same time, or pick and choose which version you want by including only the appropriate field.

      Reply

  2. Hi Mark,

    v5.1 is working fine again for my queries. Thank you very much for your outstanding support.

    Regards
    Olaf

    Reply

    1. Hi Mark,

      I’ve found a small “but”. In some circumstances, the virtual columns are not present but generate an error. It has to do with a combination of left join and order by.
      I’ve managed to find an example on standard entities:

      select a.name, co.gendercodename from quote q
      left join account a ON q.accountid = a.accountid
      join contact co on co.contactid = a.primarycontactid and co.statecode = 0
      order by a.name

      generates error message:
      Missing column co.gendercodename

      If you remove order by, then error disappears.

      Workaround is as you described for update to add column co.gendercode, then it’s doing fine.

      Regards
      Olaf

      Reply

      1. Thanks – yes I’d noticed this and I’ve got a fix ready to go for this. It works as expected when either the column is from the root table in the FetchXML or you include the underlying ID column in the SELECT clause. I’ve got a few other minor improvements to work through then I’ll probably get a patch out including this fix next week.

        Reply

  3. Hi Mark,

    I used to be able to run the following insert

    insert into teammembership ([systemuserid],[teamid])
    values (CREATELOOKUP(‘systemuser’, ‘XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX’), CREATELOOKUP(‘team’, ‘YYYYYYYY-YYYY-YYYY-YYYY-YYYYYYYYYYYY’))

    Which now gives the following which you explained above

    Unknown function: CREATELOOKUP(‘systemuser’, ‘7dc16dca-39a7-ea11-a814-00224801cfe4’)

    However, I’ve tried

    insert into teammembership ([systemuserid],[teamid])
    values (‘XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX’, ‘YYYYYYYY-YYYY-YYYY-YYYY-YYYYYYYYYYYY’)

    But that returns

    Column is not valid for INSERT: [systemuserid]

    Thanks,

    Malcolm

    Reply

      1. Thanks Mark.

        One other very minor issue that I haven’t reported before – when you go above 100 lines in the query editor the line number shows 00 rather than 100. Not causing me a problem, just an observation!

        Reply

  4. Hey Mark, first off…hat’s off to you for this amazing tool!
    I’m trying to figure out how to update existing CRM records.
    A simple test:
    update lead
    set new_attribute = ’82b739b0-f03c-e011-9a14-78e7d1644f78′,
    new_attributetype = ‘new_entity’
    where leadid = ‘1a847116-46aa-e911-8125-00505690cb20’

    …where new_attribute is a custom lookup attribute on lead entity. I figured that I should provide entity name (new_entity) so I added new_attributetype but getting the error:

    Unknown column name: new_attributetype

    1 more question….it would be great if queries like this (update/create/delete) can be run on schedule using standard Windows Task Scheduler. Have you thought about allowing that to happen?

    Best regards,
    -Tony.

    Reply

    1. The ___type virtual attributes only apply to polymorphic lookup fields such as Customer or Owner. If this is a standard lookup field that can only refer to one entity type then there will not be an associated new_attributetype field.

      I’d like to have a look at automating queries in a future version, especially for DevOps pipelines, but I haven’t started work on that yet.

      Reply

  5. Hi Mark, me again (sorry)!

    I installed the SSMS plugin version 5.1 but then when I ran SSMS 18 I got the following error on start up (from the ActivityLog.xml file).

    SetSite failed for package [Sql4CdsPackage]Source: ‘MarkMpn.Sql4Cds.SSMS’ Description: Could not load file or assembly ‘Microsoft.Xrm.Sdk, Version=9.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’ or one of its dependencies. The system cannot find the file specified. System.IO.FileNotFoundException: Could not load file or assembly ‘Microsoft.Xrm.Sdk, Version=9.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’ or one of its dependencies. The system cannot find the file specified. File name: ‘Microsoft.Xrm.Sdk, Version=9.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’at MarkMpn.Sql4Cds.SSMS.Sql4CdsPackage.d__2.MoveNext()at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)at MarkMpn.Sql4Cds.SSMS.Sql4CdsPackage.InitializeAsync(CancellationToken cancellationToken, IProgress`1 progress)at Microsoft.VisualStudio.Shell.AsyncPackage.c__DisplayClass17_0.<b__1>d.MoveNext() — End of stack trace from previous location where exception was thrown — at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()at Microsoft.VisualStudio.Services.VsTask.RethrowException(AggregateException e)at Microsoft.VisualStudio.Services.VsTask.InternalGetResult(Boolean ignoreUIThreadCheck)at Microsoft.VisualStudio.Services.VsTask.GetResult() WRN: Assembly binding logging is turned OFF. To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1. Note: There is some performance penalty associated with assembly bind failure logging. To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].

    I uninstalled that version and installed older versions but had the same issue.

    I then updated SSMS to the latest (v18.9.1), uninstalled your plugin and reinstalled again (various versions including 5.1), SSMS now starts up without any errors but if I click on the FetchXML button on any query I get the error “Object reference not set to an instance of an object” and if I try an update statement I get “UPDATE’ statement​ is not supported”

    Thanks again,

    Malcolm

    Reply

  6. Mark, as always THANK YOU for creating and maintaining this excellent tool!

    You used to be able to view the fetchXML generated by your SQL queries.

    Can you no longer view the fetchXML natively? Do I have to open FetchXML builder to view it?

    Thanks again!

    Reply

    1. If you select the FetchXML node in the execution plan and open the Properties pane which is docked to the right had side of the screen you can access the FetchXML query in there too

      Reply

  7. This tool is amazing and I use it constantly, so I first with to say Thank you!

    This issue I’m running into with the latest changes is one of convenience. I used to be able to craft a query like below, which would provide a handy link I could click on to open the UI page in question:

    select createlookup(‘systemuser’,’76082d43-abb8-dd11-8ecd-00505694052f’) from organization OR
    select createlookup(‘account’,’4a6aecfa-ff9a-e811-873d-005056940031′) from organization

    Admittedly a bit of a hack, but a super convenient hack and I’m not sure how I might replicate it’s behavior with the new configuration. Any tips? Thanks again!

    Reply

    1. You could use a query to build up the URL for you, something like:

      SELECT 'https://contoso.crm.dynamics.com/main.aspx?pagetype=entityrecord&etn=' + 'systemuser' + '&id=' + '76082d43-abb8-dd11-8ecd-00505694052f'

      You can substitute in columns instead of literal values for the entity name and ID values, e.g.:

      SELECT 'https://contoso.crm.dynamics.com/main.aspx?pagetype=entityrecord&etn=' + owneridtype + '&id=' + ownerid FROM contact

      Then you can copy & paste the URL into your browser.

      Reply

      1. Fair enough. Thanks for taking the time to reply!

        I’ll miss the one-click convenience, but it’s not that big a deal. 🙂

        Feature request: URL click to browser 😉

        Thanks again!

        Reply

  8. Hi Mark,

    i’ve just noticed that adding conditions to a join can lead to terrible execution plans. I found a simple example on standard entities just filtering on statecode:

    select a.name, contact.fullname from account a
    left join contact ON a.primarycontactid = contact.contactid and contact.statecode = 0
    where a.statecode = 0 and a.name like ‘AA%’

    The filter “contact.statecode = 0” leads to an execution plan with two fetch.xml fetching all active contacts. Without this filter, there is only one fetch.xml. Can you please have a look on this?

    Regards
    Olaf

    Reply

    1. You can set the primary key field in the same way as any other field, although you’re normally better off letting the server populate it automatically as it will generate a unique value for you. If you’re getting a duplicate key error then one or more of the values you’re already specifying must be the duplicate. This might be caused by an alternate key on the table you’re inserting into, or if you’re trying to add to a many-to-many intersect table like listmember then the two records are likely already associated and you can just not insert that record.

      Reply

  10. hi,

    it is a many to many intersect, but the record i try to insert i created just before that, so it couldn’t be already in there, i’m confused, why the … did microsoft reinvent a good concept (database) with something new. I’m doing somthing wrong and I can’t find the solution to my problem. I dislike that or me.

    Reply

  11. insert into psa_psa_project_team (psa_projectid, teamid)values(‘3c2bce65-a035-45c6-83e3-ccbec268e8ec’,’5a3c36e2-47d8-e811-a973-000d3a2bc5c1′);

    Error inserting psa_psa_project_team – Cannot insert duplicate key.
    Error inserting psa_psa_project_team – Cannot insert duplicate key.
    See the Execution Plan tab for details of where this error occurred

    Reply

    1. It certainly sounds like those records are already related. If you haven’t done that yourself it’s possible there’s some plugin or other automated process that’s added that relationship for you.

      If you do SELECT * FROM psa_psa_project_team WHERE psa_projectid = '3c2bce65-a035-45c6-83e3-ccbec268e8ec' AND teamid = '5a3c36e2-47d8-e811-a973-000d3a2bc5c1' to see the existing relationship that should show you who created that link and when.

      Reply

  12. ehhh, strangest thing the record does exist, this table does not have your typical audit columns: created on, created by, modified on, modified by etc: i get this:
    psa_projectid psa_psa_project_teamid teamid versionnumber
    3c2bce65-a035-45c6-83e3-ccbec268e8ec 1cbd22e4-639c-eb11-b1ac-000d3a48e7c8 5a3c36e2-47d8-e811-a973-000d3a2bc5c1 NULL.
    but it doesn’t show in my subform. that record psa_projectid was newly created by me and shouldn’t have this relationshiprecord. the whole point of my excersize is that i have to create 1500 projects and then link 2 to 3 teams per project so 3000 to 4000 records and i wanted to be prepared.

    Reply

  13. hoi Mark,

    forget about my question, thanks for your answers. I requested an enhancement with the developers. That will not get fixed in time, but i’ll use my workaround from last year (it requires more work). I’m in need of extra training I think.

    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.