Search code examples
t-sqlssmsdataverse

Cannot use CTE in SQL query connected to an Dataverse table (in SSMS)

I've connected to our Dataverse environment in SSMS.

I can run this simple query:

    SELECT cr6e4_customeruseradfunction, cr6e4_customeruser, count(cr6e4_assettag) as AssetCount
          FROM [dbo].[cr6e4_itassets]
          WHERE cr6e4_customeruseradfunction Is Not Null
          GROUP BY  cr6e4_customeruseradfunction, cr6e4_customeruser
    ) srcass

But if I move the subquery to a CTE, it fails:

WITH srcass as 
    (
    SELECT cr6e4_customeruseradfunction, cr6e4_customeruser, count(cr6e4_assettag) as AssetCount
          FROM [dbo].[cr6e4_itassets]
          WHERE cr6e4_customeruseradfunction Is Not Null
          GROUP BY  cr6e4_customeruseradfunction, cr6e4_customeruser
    )
SELECT * FROM srcass

with this error message:

Msg 40000, Level 16, State 1, Line 13    
'WITH' statement​ is not supported. Line:2, Position:1    
RequestId: TDS;475faaf3-0fe3-4366-9c6d-f21db0350fbf;9    
Time: 2022-02-01T13:03:02.5657490Z

Does anyone know if this is a setting in Dataverse, or just a limitation?

Solution

  • According to the Microsoft documentation here: https://learn.microsoft.com/en-us/power-apps/developer/data-platform/how-dataverse-sql-differs-from-transact-sql?tabs=not-supported#:~:text=WITH%20common_table_expression, WITH common table expressions are not supported with SQL queries targeting a Dataverse TDS endpoint.