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
Medium Priority
1,559 Views
i have this query
;with CTE(Num)
as
(
select 1
union all
select Num + 1
from CTE
where Num <= 100
)
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
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
• 2
• 2

LVL 3

Accepted Solution

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

ID: 35131587
ok, miy new query is
;with CTE(Num)
as
(
select 1
union all
select Num + 1
from CTE
where Num <= 100
)
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

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

LVL 3

Assisted Solution

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

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…
###### Suggested Courses
Course of the Month9 days, 4 hours left to enroll