When accessing database CTE view from Origam platform, there is need specify option MAXRECURSION for desired select. Is there any way to do this ? (as a parameter from Origam, not change SQL default query template)
As we discussed personally maybe the possible solution could be to run all this from a stored procedure. Were you successful on that?
Today I need CTE with maxrecursion option for Origam again. I have tried calling CTE stored procedure from view. A small tunning run
EXEC sp_serveroption 'SRVSQL\SRVSQL_INSTANCE', 'DATA ACCESS', TRUE is needed for creating view.
CREATE VIEW dbo.CTEView AS SELECT * FROM OPENQUERY([SRVSQL\SRVSQL_INSTANCE], 'EXEC DATABASE.dbo.CTEStoredProcedure')
The view now works as expected with Origam.