Let me just say, this thing is going to be great. It’s the biggest advancement in the platform for some years, and I don’t think any competitor will come close.
However, it is a preview feature, and it does have some issues. Hopefully some of these at least will get looked at before it goes GA.
Supported Syntax
I expected simple SELECT col1, ..., coln FROM table1 INNER JOIN table2 ON ... to work, but I didn’t expect to be able to use PIVOT, APPLY, sub-queries etc which do all seem to work as expected 😀
In fact, the only query syntax I regularly use that I’ve spotted not being supported yet is CTEs. This means that querying hierarchical data is still easier in FetchXML (for now), but for other ad-hoc queries then SQL seems the obvious way to go for me.
You need aliases!
You can run this query quite happily:
SELECT * FROM account
This works perfectly too:
SELECT *
FROM account AS a
INNER JOIN
contact AS c
ON a.primarycontactid = c.contactid
However, this perfectly valid SQL doesn’t:
SELECT *
FROM account
INNER JOIN
contact
ON account.primarycontactid = contact.contactid
In this case you get the error:
Msg 40000, Level 16, State 1, Line 2
The multi-part identifier "account.primarycontactid" could not be bound.
The multi-part identifier "contact.contactid" could not be bound.It appears that, whenever you use a join, the tables MUST be aliased. Not the end of the world, but in easy gotcha that could confuse new users.
Missing tables & fields
Some data is not accessible through this endpoint. My best guess is that it’s only the data that’s stored in SQL that’s available, as the ones I’ve spotted that are missing are stored elsewhere now, e.g.
- annotation.documentbody
- activitymimeattachment.body
- audit
- plugintracelog
Even more confusingly, the documentbody and body fields appear in the results if you run SELECT * FROM annotation or SELECT * FROM activitymimeattachment respectively (although they are always null), but they do not appear in the metadata for the tables.
Ideally this data would be available too, but if not I’d like some consistency between the actual results and the metadata.
OAuth timeouts
I’m not sure if this is a general problem with Active Directory authentication for SQL Server or something specific to this implementation, but once your connection has been open for an hour any more queries will receive the error:
Msg 40000, Level 16, State 1, Line 7
The OAuth token has expiredAt this point you need to force it to reconnect (in SSMS, clicking “New Query” appears to be sufficient) and carry on.
Given that this is (presumably) not running direct SQL commands, I’ve been pleasantly surprised by the range of SQL that it implements.
Other Posts
This is part of a series of posts on the T-SQL endpoint, read more about it:
2 thoughts on “CDS T-SQL endpoint pt 2 – First Thoughts”