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:
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;
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
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.
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).
Thank you Alex
You need an minimum version 9.1.0.17437 of Dynamics.
In the following documentation you find an TDS activation by command line.
https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/view-entity-data-power-bi
Hi,
Are there any limitations on the querying similar to the Fetch XML? Can I have more than 10 joins for example?
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!
You can enable TDS Endpoint without using any OrgDbOrgSettings tool.
It is available in the Power Platform Admin Center, as explained here: https://docs.microsoft.com/en-us/power-platform/admin/settings-features
With best regards,
Henrik Svensson
I would to ask if is there a way to create a linked server in SSMS for dataverse?