Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Loops Section Overview

578 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