Since I’ve been doing some investigation into the new preview T-SQL endpoint for CDS recently, I’ve decided to integrate it into SQL 4 CDS. Update to 2.1.0 to get:
Enable/Disable T-SQL Endpoint
The current official way to enable or disable the T-SQL endpoint is rather awkward, and doesn’t seem to work for me (though it does for others).
SQL 4 CDS 2.1.0 introduces a new T-SQL Endpoint item in the Object Explorer pane (if your instance is on a high-enough version). Right-click on it to enable or disable it.
Run queries using T-SQL Endpoint
Once enabled you can run SELECT queries using SQL Server Management Studio, but you can now get SQL 4 CDS to use the same endpoint for SELECT queries while still being able to run UPDATE / INSERT / DELETE statements as well.
To enable this option, open the settings dialog and tick the “Use T-SQL Endpoint where possible” option.
Now when you run a SELECT query in an instance where the T-SQL endpoint is enabled, SQL 4 CDS will use it to execute the query. Other queries will continue to work as well as they’ll be sent to the standard CDS API.
For this to work you’ll need to use an OAuth method of authenticating with CDS. You’ll know if you’ve done this as the T-SQL Endpoint item in the Object Explorer pane will show a green icon. If it’s got a yellow warning triangle, you’re using another authentication method and SQL 4 CDS won’t use the T-SQL endpoint.
When you use this new option you can run any SELECT query that the T-SQL endpoint supports, whether SQL 4 CDS can convert it to FetchXML or not, giving you all the flexibility it offers plus the support for INSERT / UPDATE / DELETE that SQL 4 CDS supports.
Hi Mark,
first of all thanks for the great tool!
Unfortunately it stopped working for me with the latest update today. I also got the newest version of XRMToolBox which was freshly installed about two weeks ago. I just wanted to let you know – don’t know if this is a general problem. I’ll try to reinstall complete XRMToolBox and give it another try.
Cheers
Carsten
Hi Carsten,
Apologies for this. This should now be working with the latest version 2.1.1.
Thanks!
Hi Mark,
I tried to upgrade the SQL 4 CDS to version 2.1 but after the installation in I get below error:
“Could not load file or assembly ‘MarkMpn.Sql4Cds.Engine, Version=2.1.0.0, Culture=neutral, PublicKeyToken=null’ or one of its dependencies. The system cannot find the file specified.”
I am using the latest version of the XrmToolBox.
Hi Geron,
Thanks, this should be fixed in version 2.1.1 which you should be able to install now.
Thanks!
Hi Mark
Is it possible in the Format function (of a SQL Query) not to remove comments?
The formatting is good, however sometimes I comment out lines and if you format it, it deletes the commented lines altogether.
Alternatively, if you have multiple statements on a single query window, could you select a piece of SQL and only format that block and not the entire page?
Thanks
Craig