The latest update is here with lots of improvements to get into!
STRING_SPLIT function
This function lets you break apart string values on some delimiter, so you can use queries like:
SELECT account.name,
v.value
FROM account
CROSS APPLY
STRING_SPLIT(account.new_multiselectpicklist, ',') AS v
As multi-select picklist fields are returned as comma-separated values, this query will return a separate row for each of the selected options.
metadata.alternate_key table
You’ve been able to query entity, attribute and relationship metadata for a while, but now you can also access alternate key details. Find those pesky keys that aren’t enabled because of some duplicate data with a query like:
SELECT entitylogicalname,
logicalname,
keyattributes
FROM metadata.alternate_key
WHERE entitykeyindexstatus <> 'Active'
EXCEPT and INTERSECT operators
These set-based operators make it easy to find records which only exist in one list and not in another, or those that exist in both. This can be very useful for comparing configuration data between instances like:
SELECT * FROM prod.metadata.entity EXCEPT SELECT * FROM uat.metadata.entity
Unlike a join which might only check if one column is the same, these operators check if every column in the result set is the same before including or excluding it. This query will highlight any entities which only exist in your prod instance and not in UAT, as well as any that have got any changes like a different display name.
Copilot improvements
There’s a lot of updates to Copilot in this release to help it feel more natural to use and make sure it’s not doing anything unexpected. There’s more explicit prompts before it runs queries, and you can see more of it “talking to itself” when it tries to run a query that fails for some reason.
Error handling
You’ll be able to more quickly see and resolve errors as you’ll get highlighting for all the errors it can find at once, rather than just one at a time as before.
For anyone using the ADO.NET connector, remember that the Sql4CdsException.Errors property can contain multiple errors!
Aggregate queries
You can group records on datetime columns like:
SELECT DATEPART(year, createdon) AS year,
DATEPART(month, createdon) AS month,
COUNT(*)
FROM account
GROUP BY DATEPART(year, createdon),
DATEPART(month, createdon)
In previous versions this would only use native FetchXML aggregation if you had selected to show dates in your local timezone, and would run much less efficiently if you were using the UTC option. This update makes use of the usertimezone option to make this run efficiently however you like to see your date values!
Sorting of aggregate queries also works more reliably now if you sort on lookup or choice columns, similar to non-aggregate queries.
Lots more!
There’s a lot of other fixes and tweaks around how SQL 4 CDS will run queries on some particular tricky tables like audit and solutioncomponent, exactly how decimal values are converted to other types for more precise SQL Server compatibility and more – check out the full release notes for details.
As always, if SQL 4 CDS helps you out please consider leaving a rating or even a donation to say thank you!
Hi Mark,
I am having issues installing 9.3. and can’t revert to 9.2
Can you share any details about the problem?
HI Mark,
I am also having issues installing 9.3 (and also 9.2). I CAN rollback to 9.1.
Once installed, upon restart a “Tools not loaded” error window appears. The errors are all with Azure.AI.OpenAI — there are 10 of them, all saying that they can’t find and load the assemblies which do seem to be in the Plugins folder after update. (System.ClientModel, Microsoft.Bcl.AsyncInterfaces, System.Diagnostics.DiagnosticSource, etc)
I hope this helps with troubleshooting!
This is an issue with the XrmToolBox plugin installer which will hopefully be fixed in the next release of that. In the meantime, after installing the new version please delete any files in the Plugins folder which also appear in the Plugins\MarkMpn.Sql4Cds folder – see some of the comments on https://markcarrington.dev/2024/07/12/sql-4-cds-v9-2-released/ for more information.
I’m getting this error message: Could not load file or assembly ‘System.Threading.Tasks.Extensions, Version=4.2.0.1, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51’ or one of its dependencies. The system cannot find the file specified.
The actual error is Azure.AI.OpenAI. Version 2.0.0.0, Culture=neutral PublicKeyToken…..
It looks like there’s CoPilot attached to the install files?
This sounds the same as this earlier comment – please check the solution to that.
hi Mark,
is there a possibility to schedule an sql script to run every day at a certain time?
You could probably do this with a PowerShell script that uses the ADO.NET provider to run the SQL, then schedule that PowerShell to run using Windows Task Scheduler or something like Azure Pipelines.
Any chance you could show us this in some more detail in some future blogpost? That would really be great!
Hi Mark, hope you’re well.
I hit the below “cast” error using v 9.0.1 when querying a custom Entity in our client’s environment.
Rightly or wrongly, I tried to get around it by installing v9.3.0, 9.2.0 and 9.1.0 but, in every case, I hit installation problems where the install window showed a Green tick for the download and a red cross for the install. I couldn’t see anywhere I could get more information on the install so I’m a bit stumped. Any ideas?
Many thanks as always for providing this brilliant tool.
Without SQL4CDS, I’m not sure I’d be willing to work on D365.
Rich
2024-09-18 10:40:45.196 AM Error MarkMpn.Sql4Cds.Engine.Sql4CdsException (0x80004005): Unable to cast object of type ‘Microsoft.SqlServer.TransactSql.ScriptDom.UserDataTypeReference’ to type ‘MarkMpn.Sql4Cds.Engine.SqlDataTypeReferenceWithCollation’. —> MarkMpn.Sql4Cds.Engine.ExecutionPlan.QueryExecutionException: Unable to cast object of type ‘Microsoft.SqlServer.TransactSql.ScriptDom.UserDataTypeReference’ to type ‘MarkMpn.Sql4Cds.Engine.SqlDataTypeReferenceWithCollation’. —> System.InvalidCastException: Unable to cast object of type ‘Microsoft.SqlServer.TransactSql.ScriptDom.UserDataTypeReference’ to type ‘MarkMpn.Sql4Cds.Engine.SqlDataTypeReferenceWithCollation’.
at MarkMpn.Sql4Cds.Engine.ExecutionPlan.SqlTypeConverter.c.b__9_22(DataSource ds, String v, DataTypeReference dt)
at MarkMpn.Sql4Cds.Engine.ExecutionPlan.SqlTypeConverter.c__DisplayClass11_0`2.b__1(DataSource ds, Object v, DataTypeReference dt)
at MarkMpn.Sql4Cds.Engine.ExecutionPlan.SqlTypeConverter.NetToSqlType(DataSource dataSource, Object value, DataTypeReference dataType)
at MarkMpn.Sql4Cds.Engine.ExecutionPlan.FetchXmlScan.OnRetrievedEntity(Entity entity, INodeSchema schema, IQueryExecutionOptions options, DataSource dataSource)
at MarkMpn.Sql4Cds.Engine.ExecutionPlan.FetchXmlScan.d__74.MoveNext()
at MarkMpn.Sql4Cds.Engine.ExecutionPlan.BaseDataNode.d__15.MoveNext()
I’m not sure what would cause the install issue, you might want to try a clean install of XrmToolBox to see if you can get around that.
If you still get this same error with the latest version, can you open an issue at https://github.com/MarkMpn/Sql4Cds/issues/new with the details of the query and the table structure it’s trying to use please?