Using SQL Management Studio with CDS

By | May 15, 2020

Just got to play with the latest cool feature, which is using SQL Management Studio to connect to CDS environments. You’ll find the documentation here:

https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/cds-sql-query

If you have not seen what it looks like, here is an example:

image

To set it up, you just need SSMS v18.4, and don’t forget to configure your environment to enable TDS endpoint. Which you can do with the help of this solution, for example:

https://github.com/seanmcne/OrgDbOrgSettings/releases

Just need to update the setting below before you try connecting SSMS to the environment;

image

And then it just works. From my very quick testing so far, here are some observations – they are not making this feature less exciting, but this is just something to keep in mind:

  • RetrieveMultiple plugins are not working when you are querying data in SSMS. There are lot of solutions which would be using those plugins for localization/security, and, it seems, SSMS is just bypassing them, so that’s something to keep in mind
  • Looking at the speed of those queries, it seems they are running directly against the database (no fetchxml involved)
  • This is not the same as having access to the on-prem database – there are no views, for example
  • If you have lots of data, I’d suggest using TOP X in your SELECT queries. Or it may take quite a bit of time to get the results
  • All data is read-only (which you can see on the first screenshot above, next to the database name). In either case, UPDATE statements etc are not supported. Which is understandable since they would have to go through the API

8 thoughts on “Using SQL Management Studio with CDS

  1. Vivek Thomas

    Hi Alex, I dont see that my region has been updated yet with this feature. Once updated with this feature what should I do to enable TDS. I am not able to see TDS listed in my settings. Are there any other ways to connect CDS to SQL DB. Thank you.

    Reply
    1. Alex Shlega Post author

      Hi Vivek,

      my guess is TDS will show up once your environment has been updated (at least I did not have to anything special in mine).

      Reply
  2. Avigail

    Hi,

    Are there any limitations on the querying similar to the Fetch XML? Can I have more than 10 joins for example?

    Reply
  3. Yash C

    Hi Alex,
    I enabled my test instance with TDS but still getting following error:
    “The server was not found or not accessible.Verify instance name is correct or SQL server is configured to allow remote connections (provider:TCP, connection could not be made because target machine actively refused it).

    I checked the connection credentials and everything was similar to the format given in MS Docs.
    Please help me!

    Reply
  4. Marcin Staszak

    I would to ask if is there a way to create a linked server in SSMS for dataverse?

    Reply

Leave a Reply to Yash C Cancel reply

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