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.