Solved

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

Posted on 2011-03-14
4
1,483 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
  • 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now