Solved

option (MAXRECURSION 1): The maximum recursion 1 has been exhausted before statement completion.

Posted on 2011-03-14
4
1,513 Views
Last Modified: 2012-06-22
i have this query
;with CTE(Num)
as
(
      select 1
      union all
      select Num + 1
      from CTE
      where Num <= 100
)
insert into dbo.Data(ADate)
      select DATEADD(day,-Num % 365, GetDate())
      from CTE
option (MAXRECURSION 0)
go

when option (MAXRECURSION 0) is OK

BUT
when option (MAXRECURSION 1) i HAVE THIS ERROR
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 1 has been exhausted before statement completion.
0
Comment
Question by:enrique_aeo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 3

Accepted Solution

by:
kraiven earned 500 total points
ID: 35130865
The MAXRECURSION option is designed for debugging of potentially infinite looping CTEs. MAXRECURSION 0 means that no Recursion level maximum is being enforced (i.e. the option is turned off) MAXRECURSION 1 means that only one recursion level is allowed. You are breaking this limit immediately by making a recursive call to the CTE item. In fact you are recursing 100 times before returning a single result.

If you change to 100 you should find it will work, but any number less will fail. does that make sense
0
 

Author Comment

by:enrique_aeo
ID: 35131587
ok, miy new query is
;with CTE(Num)
as
(
      select 1
      union all
      select Num + 1
      from CTE
      where Num <= 100
)
insert into dbo.Data(ADate)
      select DATEADD(day,-Num % 365, GetDate())
      from CTE
option (MAXRECURSION 150)
go

With the 'where I'm telling you to run 100 times, with MAXRECURSION tell him to run up to 150 times, but it does not, that is, I expected to run 50 times, and should it work?
0
 

Author Comment

by:enrique_aeo
ID: 35132095
but if it would help a good example regarding use of MAXRECURSION
0
 
LVL 3

Assisted Solution

by:kraiven
kraiven earned 500 total points
ID: 35136401
I think you have got the wrong idea about the purpose of MAXRECURSION; it is a limiting option mainly designed to prevent infinite recursive looping. It is not - as you seem to be using it - intended to define the number of loops.

I don't think you need an example you just need to remove the MAXRECURSION option from your original example and you will get 100 rows of differing dates.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question