Hi everyone!
I'm new to SQL and am trying to query a view (dbo.) made from a D365 table. My goal is to do a recursive joins on 4 different views but to start with I tried something basic and received the following error: The query references an object that is not supported in distributed processing mode. Below is my code with column/datasource names changed for privacy.
WITH F AS
(SELECT ColumnA,
ColumnB 1 AS lvl
FROM dbo.datasource
WHERE ColumnB IS NULL
UNION ALL SELECT FL.ColumnA,
FL.ColumnB,
lvl + 1 AS lvl
FROM F
INNER JOIN dbo.datasource FL ON F.ColumnA = FL.ColumnB)
SELECT *
FROM F
You cannot do this. If you want more information you should look for CTE. It would be something like this:
;WITH F AS
(SELECT ColumnA,
ColumnB 1 AS lvl
FROM dbo.datasource
WHERE ColumnB IS NULL
), F2 AS
(
SELECT FL.ColumnA, FL.ColumnB,lvl + 1 AS lvl
FROM F
INNER JOIN dbo.datasource FL ON F.ColumnA = FL.ColumnB
)
SELECT *
FROM F2;
This error usually shows up when you're running the query in a distributed SQL environment where certain objects or system views aren’t supported. Try running just a simple SELECT Col1 FROM YourView without the recursive CTE. If this fails, the view itself isn’t supported in that environment. Else, if it works, then the recursive logic or underlying object may be the issue. If you're expecting full SQL Server behavior, make sure you're connected to a dedicated SQL instance, not a distributed or serverless endpoint.