Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-03-14
4
Medium Priority
?
1,559 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 2000 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 2000 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

604 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