Accessing CTE view - maxrecursion

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.

1 Like