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

• • Reply
  

  urir10

  Member

  93 Points

  225 Posts

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

  Aug 24, 2010 02:59 PM|LINK

  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

  
  
  

• • Reply
  

  ketan_al

  Contributor

  6850 Points

  1143 Posts

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

  Aug 24, 2010 04:29 PM|LINK

  Hi,

  Please refer following

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

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

  hope this helps
  

  
  

  Ketan A. Lohar
  MCP, MCTS ( ASP.NET 3.5 )
  
  Please mark as answer if it helps :)
• • Reply
  

  urir10

  Member

  93 Points

  225 Posts

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

  Aug 24, 2010 05:21 PM|LINK

  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?

• • Reply
  

  kpyap

  Contributor

  5212 Points

  989 Posts

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

  Aug 25, 2010 03:51 AM|LINK

  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

• • Reply
  

  urir10

  Member

  93 Points

  225 Posts

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

  Aug 25, 2010 02:15 PM|LINK

  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.

  • Marked as answer by Hong-Gang Chen - MSFT on Aug 30, 2010 09:14 AM
• • Reply
  

  selcuksarlak

  Member

  2 Points

  1 Post

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

  Jul 02, 2012 12:21 PM|LINK

  thank birader....