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