How to DirectQuery View with Recursive Query CTE

[bellr]

Hi all,

Thanks for taking a look at the question!

I've got a View which contains a recursive CTE to generate dates between a given start and end date.

WITH D AS (
	SELECT CAST(DATEADD(month, DATEDIFF(month, 0, DATEADD(m, 0 - 12, GETUTCDATE())), 0) AS DATETIME) as DayDate
	UNION ALL
	SELECT DATEADD(day, 1, DayDate)
	FROM D
	WHERE DayDate < CAST(DATEADD(month, DATEDIFF(month, 0, GETUTCDATE()), 0) AS DATETIME)
)

There's some other SQL code that joins onto that CTE.

Usually when executing this query I'd include 

OPTION (MAXRECURSION 0)

at the end of the query, if you don't SQL Server will error.

I'm porting this query to Power BI but can't find a way to constraint the MAXRECURSION option like I would in a standard SQL statement.

I can't include this in the View because it'll error (just like an OrderBy)

I can't see a way to edit the query once I've created a SQL Server Data Set querying the View

When I navigate though 'Get Data' -> SQL Server -> Advanced Options (DirectQuery ticked) then enter a query such as

SELECT * FROM MyView OPTION(MAXRECURSION 0)

The query executes and you see a result set, but then when I click Load to create the data set I get an error stating: "Microsoft SQL: Incorrect syntax near the keyword 'OPTION'."

Any ideas as to what my options are here, and if there's a best practice for this kind of query?

Thanks in advance & kind regards,

Rob

[v-chuncz-msft · Accepted solution]

@bellr,

You may refer to the post below that makes use of OPENROWSET.

https://community.powerbi.com/t5/Desktop/Calling-SQL-stored-procedure-with-Direct-Query/td-p/224831

[thentangler]

The link you specified does not have a sloution.