Copilot for Sales, Copilot for Service, Copilot for… SQL 4 CDS?

Yes, you can now have a Copilot for your SQL queries! It can help write, explain and improve your queries, and even run them and interpret the results.

Who has created the most contact records recently?

To find out which user has created the most contact records recently, we’ll follow these steps:

  1. Identify the contacts created within a recent timeframe.
  2. Group these contacts by the user who created them.
  3. Count the number of contacts for each user.
  4. Order the result to get the user with the maximum count.

Let’s first check the schema of the contact table to understand which columns are available for filtering and grouping. Specifically, we need the creation date and the user who created the record.

I’ll get the columns for the contact table.

We have the necessary columns for our query:

  • createdby to identify the user who created the record.
  • createdon to filter records by their creation date.

Now, let’s write a query to find the user who has created the most contact records recently.

Copy Code
-- Find the user who has created the most contact records recently
WITH RecentContacts AS (
    SELECT
        createdby,
        COUNT(contactid) AS contact_count
    FROM
        contact
    WHERE
        createdon >= DATEADD(day, -30, GETDATE())  -- Adjust the timeframe as needed, e.g., last 30 days
    GROUP BY
        createdby
)
SELECT
    rc.createdby,
    su.fullname,
    rc.contact_count
FROM
    RecentContacts rc
    JOIN systemuser su ON rc.createdby = su.systemuserid
ORDER BY
    rc.contact_count DESC
FETCH FIRST 1 ROWS ONLY;  -- Get the user with the most contact records created

Explanation:

  • The CTE RecentContacts filters contact records created in the last 30 days and counts the number of records created by each user (createdby).
  • The main SELECT query joins this CTE with the systemuser table to fetch the full name of the user.
  • The result is ordered by contact_count in descending order.
  • Finally, we fetch only the top user.

Would you like to specify a different timeframe or any additional details?

The suggested query contains errors:

Incorrect syntax near FIRST.

The FETCH FIRST clause is not supported. Instead, we can use TOP 1 to retrieve the user with the most contact records.

Here is the corrected query:

Copy Code
-- Find the user who has created the most contact records recently
WITH RecentContacts AS (
    SELECT
        createdby,
        COUNT(contactid) AS contact_count
    FROM
        contact
    WHERE
        createdon >= DATEADD(day, -30, GETDATE())  -- Adjust the timeframe as needed, e.g., last 30 days
    GROUP BY
        createdby
)
SELECT TOP 1
    rc.createdby,
    su.fullname,
    rc.contact_count
FROM
    RecentContacts rc
    JOIN systemuser su ON rc.createdby = su.systemuserid
ORDER BY
    rc.contact_count DESC;

This query selects the top user who created the most contact records within the last 30 days.

Would you like to proceed with this query?

Executing the query:

Copy Code
WITH RecentContacts AS ( SELECT createdby, COUNT(contactid) AS contact_count FROM contact WHERE createdon >= DATEADD(day, -30, GETDATE()) GROUP BY createdby ) SELECT TOP 1 rc.createdby, su.fullname, rc.contact_count FROM RecentContacts rc JOIN systemuser su ON rc.createdby = su.systemuserid ORDER BY rc.contact_count DESC;

The query has successfully executed, and the user who has created the most contact records recently is:

  • Created By (User ID): b7e68613-74b8-4c05-9ff4-8c3c418feb1a
  • Full Name: Mark Carrington
  • Contact Count: 252

This user, Mark Carrington, has created the highest number of contact records (252) within the specified recent timeframe.

This is an example of a real conversion with the Copilot. If you scroll through it you’ll see it generated a query and explained how it worked, but SQL 4 CDS automatically validated that query and identified that it contained some invalid syntax. I then clicked the Retry button to feed the error details back to the Copilot which then allowed it to generate the correct query. One more button click then ran the query and interpreted the results for me.

I’d love to see how use this feature and where you find it works well and where it doesn’t. If you find it regularly needs some extra prompts to get to a working query it should be possible to tweak the prompts it is using to get there faster.

The Copilot is aware of the context of the conversation, so you can ask followup questions in a natural way. It also understands the query you currently have open or selected. This lets you ask questions like “Where is the error in this query?” or “How could I make this query run faster?”

Setting up Copilot

As you might have noticed already, the AI compute behind Copilots doesn’t come free, and this is no different. For now you will need to provide your own OpenAI or Azure OpenAI account details to use.

If you want to use Azure OpenAI you will first need to create a model deployment and get its endpoint URL. You’ll get the best results using the latest model gpt-4o, but any model that supports the Assistant API will work. Whether you’re using OpenAI or Azure OpenAI, you’ll also need an API key.

Armed with these bits of information we can start setting up Copilot in SQL 4 CDS. Open the Settings window and go to the new Copilot tab.

If you’re using Azure OpenAI, enter the endpoint URL in the first box. For OpenAI you can leave this blank. Put your API key into the second box, then click the “Create” button to create the copilot assistant. This will ask for the model to use – for Azure OpenAI, enter the name of the model deployment you created, or for OpenAI enter the underlying model name like gpt-4o

Save the settings and you should now see the Copilot panel on the right hand side of your SQL queries.

There is also a setting here “Allow Copilot to execute SELECT queries independently”. With this unticked (the default), if you ask a question like “How many contacts do I have?” the Copilot will reply with a query that you can choose to run or not. If you do tick this, it could instead run whatever query it generates before responding with “You have 123 contacts”. Copilot can only run SELECT queries like this – any other types of queries will be blocked and you will have to explicitly choose to run them yourself.

Other UI Improvements

As well as Copilot, you can now also right-click on tables, functions and stored procedures in the Object Explorer pane to generate a script to query them, similar to SSMS.

You can also right-click on the results table to save the results as CSV or Excel formats. In Azure Data Studio you also get the options for other formats like Markdown, JSON and XML.

Query Engine Fixes & Improvements

This release contains all manner of improvements to the underlying engine for particular queries. In particular you should now see more accurate results when you query virtual tables, the audit table and metadata tables.

One good example of this is investigating recent solution imports – the equivalent of viewing the solution history page in the maker portal. This used to be available in the solutionhistorydata table, but querying this table now gives an error. Instead we can use the msdyn_solutionhistory virtual table, but this would ignore any WHERE, ORDER BY or TOP constraints you try to apply. If you want to investigate recent imports you might write:

SELECT TOP 10 *
FROM     msdyn_solutionhistory
WHERE    msdyn_starttime > '2024-07-11'
ORDER BY msdyn_starttime

This would be translated to perfectly valid FetchXML, but the virtual table provider appears to ignore those details and just gives 5000 results without any filtering and in whatever order it sees fit.

To avoid this the SQL 4 CDS engine now keeps all its extra processing pipeline intact in case the virtual table provider does not support any of the requested operations, so although this is less efficient you should end up with the results you requested.

The query engine can now also identify simple cases where filters are always true and can be removed, or are always false and therefore there is no need to touch the database at all. For example, these queries:

SELECT name
FROM   account
WHERE  accountid IS NOT NULL;

SELECT name
FROM   account
WHERE  accountid IS NULL;

The criteria for the first query is always true, so this results in a simple FetchXML:

<fetch>
  <entity name='account'>
    <attribute name='name' />
  </entity>
</fetch>

The second query will never return any records, so this doesn’t generate any FetchXML at all. Instead it will immediately return an empty result.

This analysis is at an early stage at the moment so there will be plenty of cases it doesn’t pick up and still does more processing than it needs to do, but you should still get the correct results as before.

16 thoughts on “SQL 4 CDS v9.2 Released”

      1. when xrmtoolbox restart, i get this errors
        https://i.imgur.com/bZuKbBV.png

        Here you can find the 3 messages (in french)
        Impossible de charger le fichier ou l’assembly ‘System.ClientModel, Version=1.0.0.0, Culture=neutral, PublicKeyToken=92742159e12e44c8’ ou une de ses dépendances. Le fichier spécifié est introuvable.
        Impossible de charger le fichier ou l’assembly ‘Microsoft.Bcl.AsyncInterfaces, Version=1.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51’ ou une de ses dépendances. Le fichier spécifié est introuvable.
        Impossible de charger le fichier ou l’assembly ‘System.Diagnostics.DiagnosticSource, Version=6.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51’ ou une de ses dépendances. Le fichier spécifié est introuvable.

        and the final error :
        System.Reflection.ReflectionTypeLoadException
        mscorlib

        à System.Reflection.RuntimeModule.GetTypes(RuntimeModule module)
        à System.Reflection.RuntimeModule.GetTypes()
        à System.Reflection.Assembly.GetTypes()
        à System.ComponentModel.Composition.Hosting.AssemblyCatalog.get_InnerCatalog()
        à System.ComponentModel.Composition.Hosting.AssemblyCatalog.GetExports(ImportDefinition definition)
        à System.Linq.Enumerable.d__17`2.MoveNext()
        à System.ComponentModel.Composition.Hosting.CatalogExportProvider.InternalGetExportsCore(ImportDefinition definition, AtomicComposition atomicComposition)
        à System.ComponentModel.Composition.Hosting.ExportProvider.TryGetExportsCore(ImportDefinition definition, AtomicComposition atomicComposition, IEnumerable`1& exports)
        à System.ComponentModel.Composition.Hosting.ExportProvider.TryGetExports(ImportDefinition definition, AtomicComposition atomicComposition, IEnumerable`1& exports)
        à System.ComponentModel.Composition.Hosting.CatalogExportProvider.GetExportsCore(ImportDefinition definition, AtomicComposition atomicComposition)
        à System.ComponentModel.Composition.Hosting.ExportProvider.TryGetExportsCore(ImportDefinition definition, AtomicComposition atomicComposition, IEnumerable`1& exports)
        à System.ComponentModel.Composition.Hosting.ExportProvider.GetExports(ImportDefinition definition, AtomicComposition atomicComposition)
        à System.ComponentModel.Composition.Hosting.AggregateExportProvider.GetExportsCore(ImportDefinition definition, AtomicComposition atomicComposition)
        à System.ComponentModel.Composition.Hosting.ExportProvider.TryGetExportsCore(ImportDefinition definition, AtomicComposition atomicComposition, IEnumerable`1& exports)
        à System.ComponentModel.Composition.Hosting.CompositionContainer.GetExportsCore(ImportDefinition definition, AtomicComposition atomicComposition)
        à System.ComponentModel.Composition.Hosting.ExportProvider.TryGetExportsCore(ImportDefinition definition, AtomicComposition atomicComposition, IEnumerable`1& exports)
        à System.ComponentModel.Composition.Hosting.ExportProvider.GetExports(ImportDefinition definition, AtomicComposition atomicComposition)
        à System.ComponentModel.Composition.Hosting.ImportEngine.TryGetExports(ExportProvider provider, ComposablePart part, ImportDefinition definition, AtomicComposition atomicComposition)
        à System.ComponentModel.Composition.Hosting.ImportEngine.TrySatisfyImportSubset(PartManager partManager, IEnumerable`1 imports, AtomicComposition atomicComposition)
        à System.ComponentModel.Composition.Hosting.ImportEngine.TrySatisfyImportsStateMachine(PartManager partManager, ComposablePart part)
        à System.ComponentModel.Composition.Hosting.ImportEngine.TrySatisfyImports(PartManager partManager, ComposablePart part, Boolean shouldTrackImports)
        à System.ComponentModel.Composition.Hosting.ImportEngine.SatisfyImports(ComposablePart part)
        à System.ComponentModel.Composition.Hosting.CompositionServices.TryInvoke(Action action)
        à System.ComponentModel.Composition.Hosting.ComposablePartExportProvider.Compose(CompositionBatch batch)
        à XrmToolBox.PluginManagerExtended.LoadParts(Boolean isRetry)
        à XrmToolBox.PluginManagerExtended.LoadParts(Boolean isRetry)
        à XrmToolBox.PluginManagerExtended.RescanIfRequired()
        à XrmToolBox.PluginManagerExtended.Initialize(Form parentForm)
        à XrmToolBox.New.PluginsForm2..ctor()
        à XrmToolBox.New.NewForm..ctor(String[] args)
        à XrmToolBox.Program.Main(String[] args)

        Reply

        1. It sounds like some of the extra assemblies may have been added to your main Plugins folder, rather than a subfolder. Files like Azure.AI.OpenAI.dll should only be in the %appdata%\MscrmTools\XrmToolBox\Plugins\MarkMpn.Sql4Cds folder, not in the %appdata%\MscrmTools\XrmToolBox\Plugins folder itself. You can try deleting these extra files and see if XrmToolBox will then load as expected. Some earlier versions of XrmToolBox would put these files in both folders rather than just the MarkMpn.Sql4Cds subfolder.

          Reply

  2. Thanks for this great tool! Couple of questions/issues:

    1) For the Copilot feature, I generated an OpenAI key and entered it, then entered “gpt-4o” for Assistant ID. I’m getting error “HTTP 400 (invalid_request_error: model_not_found) Parameter: model The requested model ‘gpt-4o’ does not exist.”.

    2) I have a virtual table that uses Microsoft’s Virtual Table Connector Provider. When I try querying with “select top *” (or select top any column(s)), I only get back one record, even though the Azure SQL table has millions of records.

    Any thoughts/guidance on these?

    Reply

    1. The models you have available from Open AI may vary depending on your account – please check your Open AI account for a list of the models you have available to you.

      Can you please share the full query you’re running where you’re not getting the expected results?

      Reply

  3. Hi Mark,
    I got an error while trying to configure an Azure Open AI with gpt-4o. On the “Create Copilot Assistant” window, after adding the name of my deployment and clicking OK, it gets:

    “HTTP 404 (invalid_request_error: )

    Invalid URL (POST /v1/assistants)”

    I also tryied with GPT 35, same error.

    Reply

    1. Can you please check that you have entered the endpoint of your Azure Open AI instance in the format https://resource-name.openai.azure.com/ on the settings page above the Open AI key, and the model name you have entered on the Create Copilot Assistant window matches the name of your model deployment (not the name of the model itself) that you created in Azure Open AI Studio?

      Reply

  4. Unfortunately this release crashes XRMToolBox on startup for me. How do I roll back to the previous version (which worked fine) ?

    Could not load file or assembly ‘System.ClientModel, Version=1.0.0.0, Culture=neutral, PublicKeyToken=92742159e12e44c8’ or one of its dependencies. The system cannot find the file specified.

    Could not load file or assembly ‘Microsoft.Bcl.AsyncInterfaces, Version=1.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51’ or one of its dependencies. The system cannot find the file specified.

    Could not load file or assembly ‘System.ClientModel, Version=1.0.0.0, Culture=neutral, PublicKeyToken=92742159e12e44c8’ or one of its dependencies. The system cannot find the file specified.

    Could not load file or assembly ‘System.Diagnostics.DiagnosticSource, Version=6.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51’ or one of its dependencies. The system cannot find the file specified.

    These .dlls do appear to be in the AppData\Roaming\MscrmTools\XrmToolBox\Plugins\MarkMpn.Sql4Cds folder though so I’m not sure why it can’t access them. My user has full filesystem permissions to the files and even running XrmToolbox elevated as Administrator still crashes with a System.Reflection.ReflectionTypeLoadException error.

    Reply

  5. Ok I think I fixed my XrmToolBox installation.

    I think the installer for this update is also copying files to this folder:
    \AppData\Roaming\MscrmTools\XrmToolBox\Plugins

    instead of just this folder:
    AppData\Roaming\MscrmTools\XrmToolBox\Plugins\MarkMpn.Sql4Cds

    After removing all of the folder last modified today (when I installed the Sql4CDS update) and then tried running XRMToolBox again this time it didn’t crash….at the cost of removing SQL4CDS from my XRMToolBox installation.

    But if I try running the SQL4CDS installer for the new version again (to add it back again) the same thing happens. The files are copied to the “\AppData\Roaming\MscrmTools\XrmToolBox\Plugins” folder.

    Reply

    1. After installing SQL 4 CDS, can you please try removing any files from the Plugins folder which are also present in the Plugins\MarkMpn.Sql4Cds folder? The only file from SQL 4 CDS which should be in the main Plugins folder is MarkMpn.Sql4Cds.dll

      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.