The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Hi All

I have the below sql scripts that give me the error:
"The statement terminated. The maximum recursion 100 has been exhausted before statement completion."

Ive tried using OPTION ( MAXRECURSION 0 ) or 1000 but it still give me the same error.

WITH cte as (SELECT     ticketid, setstatus, setdate, row_number() OVER (partition BY (ticketid)
ORDER BY setdate ASC) AS rn
FROM         [OneForm].[dbo].[TFAM_SLA_Times]
UNION
SELECT     TicketID, 'Submitted', SubmittedON, 0
FROM         [OneForm].[dbo].vw_TFAM_ALL)

    SELECT     a.ticketid, SUM(OneForm.dbo.[CalcMinutesSLA_TFAM](a.setdate, b.setdate)) AS SLA_in_Min
     FROM         vw_TFAM_SLA_Combined AS a LEFT JOIN
                            vw_TFAM_SLA_Combined AS b ON a.ticketid = b.ticketid AND a.rn = b.rn - 1
     WHERE     (a.SetStatus = 'Submitted' OR
                            a.SetStatus = 'Acknowledged' OR
                            a.SetStatus = 'Pending')
                            
     GROUP BY a.TicketID

If i remove the call to the function [CalcMinutesSLA_TFAM] or the b.rn - 1 it works fine.

Here is the code for the function:

CREATE FUNCTION [dbo].[CalcMinutesSLA2] (
 @startTime DATETIME,
 @endTime DATETIME
 ) 
returns int
as
begin
   declare @minutes int;
   
   -- you should only need to alter these two to change work times
   declare @wdStartMins int, @wdLength int
   set @wdStartMins = 510; -- 8.5 hours from 00:00 or 8:30 am
   set @wdLength = 510; -- 8.5 hours (e.g., 8:30 am - 5:00 pm)

   declare @fwd datetime, @lwd datetime
   set @fwd = dateadd(mi, @wdStartMins, datediff(d, 0, @startTime));
   if (@fwd < @startTime) set @fwd = @startTime;
      
   set @lwd = dateadd(mi, @wdStartMins+@wdLength, datediff(d, 0, @endTime));
   if (@lwd > @endTime) set @lwd = @endTime;
   
   ;with cte(st, et)
   as
   (
      select @fwd
           , case datediff(d, @fwd, @lwd) 
                when 0 then @lwd 
                else dateadd(mi, @wdStartMins+@wdLength, datediff(d, 0, @fwd)) 
             end
      union all
      select dateadd(mi, @wdStartMins, datediff(d, 0, st+1))
           , case datediff(d, st+1, @lwd) 
                when 0 then @lwd 
                else dateadd(mi, @wdStartMins+@wdLength, datediff(d, 0, st+1)) 
             end
      from cte
      where dateadd(mi, @wdStartMins, datediff(d, 0, st+1)) < @lwd
   )
    select @minutes = sum(datediff(mi, st, et))
   from cte C LEFT JOIN web_holidays H ON CONVERT(varchar,C.st,101) = H.HolidayDate   
   where left(datename(dw, st), 1) <> 'S'
   and st < et
   and H.HolidayDate IS NULL
   ;
   
   return coalesce(@minutes, 0);
end

Re: The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Hi,

Please refer following

http://michaeljswart.com/?tag=recursive-cte

http://sqlserverpedia.com/wiki/CTE_-_Recursive_CTE_Internals

hope this helps

Re: The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Thanks for the reply ketan_al

Those two article only explain what recursion is which i know but i just cant fix the current code i have to avoid that error.

Any suggestions?

Re: The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Hi,

I tried start and end date where end date is one day after start date, and it seem no error.

Please provide some sample input and data to simulate the error.

E.g.
a) Holiday table data
b) Start and end date

Re: The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

finnaly fixed it.

i ended up using the MAXRECURSION just before i was putting in the wrong place, i had to use it in the function.

Re: The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

thank birader....