Solved

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

Posted on 2010-08-20
14
2,307 Views
Last Modified: 2013-11-30
Hi i have the below sql code and i keep getting the error   "The statement terminated. The maximum recursion 100 has been exhausted before statement completion"

Any ideas why?
WITH cte AS (SELECT     dbo.vw_TFAM_ALL.TicketID, dbo.vw_TFAM_ALL.CC, dbo.vw_TFAM_ALL.FormType, dbo.vw_TFAM_ALL.SubmittedOn, 

                                                    dbo.vw_TFAM_ALL.AcknBy, dbo.vw_TFAM_ALL.AcknOn, dbo.vw_TFAM_ALL.SubmittedBy, dbo.vw_TFAM_ALL.Status, 

                                                    dbo.vw_TFAM_ALL.isSubmitted, dbo.vw_TFAM_ALL.IndvACF2ID, dbo.vw_TFAM_ALL.PendOrPost, dbo.vw_TFAM_ALL.NoOfEmpl, 

                                                    dbo.vw_TFAM_ALL.Business, dbo.vw_TFAM_ALL.ReqType, CASE WHEN [OneForm].[dbo].vw_TFAM_ALL.Status = 'Fulfilled' OR

                                                    [OneForm].[dbo].vw_TFAM_ALL.Status = 'Cancelled' THEN [OneForm].[dbo].[vw_TFAM_SLA_Times].SLA_in_Min ELSE NULL 

                                                    END AS SLA_Minutes, CASE WHEN [OneForm].[dbo].vw_TFAM_ALL.Status = 'Fulfilled' OR

                                                    [OneForm].[dbo].vw_TFAM_ALL.Status = 'Cancelled' THEN [OneForm].[dbo].[vw_TFAM_SLA_Times].SLA_in_Min / 1440 ELSE NULL 

                                                    END AS SLA_Days

                             FROM         dbo.vw_TFAM_SLA_Times INNER JOIN

                                                    dbo.vw_TFAM_ALL ON dbo.vw_TFAM_ALL.TicketID = dbo.vw_TFAM_SLA_Times.ticketid)







    SELECT     TicketID, CC, FormType, SubmittedOn, AcknBy, AcknOn, SubmittedBy, Status, isSubmitted, IndvACF2ID, PendOrPost, NoOfEmpl, Business, ReqType, 

                            SLA_Minutes, SLA_Days, CASE WHEN FormType = 'BuildingPass' AND SLA_Days <= 3 THEN 1 WHEN FormType = 'CancelAccess' AND 

                            SLA_Days <= 4 THEN 1 WHEN FormType = 'NameChange' AND SLA_Days <= 10 THEN 1 WHEN FormType = 'ChangeAccess' AND 

                            SLA_Days <= 10 THEN 1 WHEN FormType = 'ETCChange' AND SLA_Days <= 3 THEN 1 WHEN FormType = 'EmpStatusChange' AND 

                            SLA_Days <= 3 THEN 1 WHEN FormType = 'HomeAccess' AND ReqType = 'New Access' AND 

                            SLA_Days <= 15 THEN 1 WHEN FormType = 'HomeAccess' AND ReqType = 'Add Application' AND 

                            SLA_Days <= 15 THEN 1 WHEN FormType = 'HomeAccess' AND ReqType = 'Cancel Home Access' AND 

                            SLA_Days <= 10 THEN 1 WHEN FormType = 'HomeAccess' AND ReqType = 'Token Replacement – Lost/Damaged/Expired' AND 

                            SLA_Days <= 15 THEN 1 WHEN FormType = 'NewHire' AND SLA_Days <= 10 THEN 1 WHEN FormType = 'PassReset' AND 

                            SLA_Days <= 3 THEN 1 WHEN FormType = 'ReactivationAccess' AND SLA_Days <= 3 THEN 1 WHEN FormType = 'SharedDrive' AND 

                            SLA_Days <= 7 THEN 1 WHEN FormType = 'SuspendAccess' AND SLA_Days <= 3 THEN 1 WHEN FormType = 'TMChange' AND 

                            SLA_Days <= 5 THEN 1 WHEN FormType = 'TransferIn' AND SLA_Days <= 10 THEN 1 WHEN FormType = 'TransferOut' AND 

                            SLA_Days <= 4 THEN 1 WHEN FormType = 'PositionChange' AND SLA_Days <= 10 THEN 1 WHEN FormType = 'AccessExc' AND 

                            SLA_Days <= 10 THEN 1 WHEN FormType = 'GenericEmail' AND ReqType = 'Create New Generic Mail ID' AND 

                            SLA_Days <= 10 THEN 1 WHEN FormType = 'GenericEmail' AND ReqType = 'Delete Generic Mail ID' AND 

                            SLA_Days <= 5 THEN 1 WHEN FormType = 'GenericEmail' AND ReqType = 'Create New Distribution List' AND 

                            SLA_Days <= 7 THEN 1 WHEN FormType = 'GenericEmail' AND ReqType = 'Delete Distribution List' AND 

                            SLA_Days <= 5 THEN 1 WHEN FormType = 'GenericEmail' AND ReqType = 'Change Distribution List' AND SLA_Days <= 5 THEN 1 ELSE NULL 

                            END AS SLA_Met, 

                            CASE WHEN FormType = 'AccessExc' THEN 'Access - Exception Process' WHEN FormType = 'BuildingPass' THEN 'Building Pass' WHEN FormType

                             = 'CancelAccess' THEN 'Cancel Access' WHEN FormType = 'ChangeAccess' THEN 'Change Access' WHEN FormType = 'EmpStatusChange' THEN 'Employment Status Change'

                             WHEN FormType = 'ETCChange' THEN 'Change to ETC Admin DB' WHEN FormType = 'GenericEmail' THEN 'Generic Email' WHEN FormType = 'HomeAccess'

                             THEN 'Home Access' WHEN FormType = 'NameChange' THEN 'Name Change' WHEN FormType = 'NewHire' THEN 'New Hire' WHEN FormType = 'PassReset'

                             THEN 'Password Re-set' WHEN FormType = 'PositionChange' THEN 'Position/Role Change' WHEN FormType = 'ReactivationAccess' THEN 'Reactivation Access'

                             WHEN FormType = 'SharedDrive' THEN 'Shared Drive Access' WHEN FormType = 'SuspendAccess' THEN 'Suspend Access' WHEN FormType = 'TMChange'

                             THEN 'Team Manager Change Only' WHEN FormType = 'TransferIn' THEN 'Transfer In' WHEN FormType = 'TransferOut' THEN 'Transfer Out' END AS

                             FormName

     FROM         cte AS cte_1

Open in new window

0
Comment
Question by:urir10
  • 7
  • 5
14 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33488924
You can do this

WITH cte AS (SELECT     dbo.vw_TFAM_ALL.TicketID, dbo.vw_TFAM_ALL.CC, dbo.vw_TFAM_ALL.FormType, dbo.vw_TFAM_ALL.SubmittedOn,
                                                    dbo.vw_TFAM_ALL.AcknBy, dbo.vw_TFAM_ALL.AcknOn, dbo.vw_TFAM_ALL.SubmittedBy, dbo.vw_TFAM_ALL.Status,
                                                    dbo.vw_TFAM_ALL.isSubmitted, dbo.vw_TFAM_ALL.IndvACF2ID, dbo.vw_TFAM_ALL.PendOrPost, dbo.vw_TFAM_ALL.NoOfEmpl,
                                                    dbo.vw_TFAM_ALL.Business, dbo.vw_TFAM_ALL.ReqType, CASE WHEN [OneForm].[dbo].vw_TFAM_ALL.Status = 'Fulfilled' OR
                                                    [OneForm].[dbo].vw_TFAM_ALL.Status = 'Cancelled' THEN [OneForm].[dbo].[vw_TFAM_SLA_Times].SLA_in_Min ELSE NULL
                                                    END AS SLA_Minutes, CASE WHEN [OneForm].[dbo].vw_TFAM_ALL.Status = 'Fulfilled' OR
                                                    [OneForm].[dbo].vw_TFAM_ALL.Status = 'Cancelled' THEN [OneForm].[dbo].[vw_TFAM_SLA_Times].SLA_in_Min / 1440 ELSE NULL
                                                    END AS SLA_Days
                             FROM         dbo.vw_TFAM_SLA_Times INNER JOIN
                                                    dbo.vw_TFAM_ALL ON dbo.vw_TFAM_ALL.TicketID = dbo.vw_TFAM_SLA_Times.ticketid)



    SELECT     TicketID, CC, FormType, SubmittedOn, AcknBy, AcknOn, SubmittedBy, Status, isSubmitted, IndvACF2ID, PendOrPost, NoOfEmpl, Business, ReqType,
                            SLA_Minutes, SLA_Days, CASE WHEN FormType = 'BuildingPass' AND SLA_Days <= 3 THEN 1 WHEN FormType = 'CancelAccess' AND
                            SLA_Days <= 4 THEN 1 WHEN FormType = 'NameChange' AND SLA_Days <= 10 THEN 1 WHEN FormType = 'ChangeAccess' AND
                            SLA_Days <= 10 THEN 1 WHEN FormType = 'ETCChange' AND SLA_Days <= 3 THEN 1 WHEN FormType = 'EmpStatusChange' AND
                            SLA_Days <= 3 THEN 1 WHEN FormType = 'HomeAccess' AND ReqType = 'New Access' AND
                            SLA_Days <= 15 THEN 1 WHEN FormType = 'HomeAccess' AND ReqType = 'Add Application' AND
                            SLA_Days <= 15 THEN 1 WHEN FormType = 'HomeAccess' AND ReqType = 'Cancel Home Access' AND
                            SLA_Days <= 10 THEN 1 WHEN FormType = 'HomeAccess' AND ReqType = 'Token Replacement – Lost/Damaged/Expired' AND
                            SLA_Days <= 15 THEN 1 WHEN FormType = 'NewHire' AND SLA_Days <= 10 THEN 1 WHEN FormType = 'PassReset' AND
                            SLA_Days <= 3 THEN 1 WHEN FormType = 'ReactivationAccess' AND SLA_Days <= 3 THEN 1 WHEN FormType = 'SharedDrive' AND
                            SLA_Days <= 7 THEN 1 WHEN FormType = 'SuspendAccess' AND SLA_Days <= 3 THEN 1 WHEN FormType = 'TMChange' AND
                            SLA_Days <= 5 THEN 1 WHEN FormType = 'TransferIn' AND SLA_Days <= 10 THEN 1 WHEN FormType = 'TransferOut' AND
                            SLA_Days <= 4 THEN 1 WHEN FormType = 'PositionChange' AND SLA_Days <= 10 THEN 1 WHEN FormType = 'AccessExc' AND
                            SLA_Days <= 10 THEN 1 WHEN FormType = 'GenericEmail' AND ReqType = 'Create New Generic Mail ID' AND
                            SLA_Days <= 10 THEN 1 WHEN FormType = 'GenericEmail' AND ReqType = 'Delete Generic Mail ID' AND
                            SLA_Days <= 5 THEN 1 WHEN FormType = 'GenericEmail' AND ReqType = 'Create New Distribution List' AND
                            SLA_Days <= 7 THEN 1 WHEN FormType = 'GenericEmail' AND ReqType = 'Delete Distribution List' AND
                            SLA_Days <= 5 THEN 1 WHEN FormType = 'GenericEmail' AND ReqType = 'Change Distribution List' AND SLA_Days <= 5 THEN 1 ELSE NULL
                            END AS SLA_Met,
                            CASE WHEN FormType = 'AccessExc' THEN 'Access - Exception Process' WHEN FormType = 'BuildingPass' THEN 'Building Pass' WHEN FormType
                             = 'CancelAccess' THEN 'Cancel Access' WHEN FormType = 'ChangeAccess' THEN 'Change Access' WHEN FormType = 'EmpStatusChange' THEN 'Employment Status Change'
                             WHEN FormType = 'ETCChange' THEN 'Change to ETC Admin DB' WHEN FormType = 'GenericEmail' THEN 'Generic Email' WHEN FormType = 'HomeAccess'
                             THEN 'Home Access' WHEN FormType = 'NameChange' THEN 'Name Change' WHEN FormType = 'NewHire' THEN 'New Hire' WHEN FormType = 'PassReset'
                             THEN 'Password Re-set' WHEN FormType = 'PositionChange' THEN 'Position/Role Change' WHEN FormType = 'ReactivationAccess' THEN 'Reactivation Access'
                             WHEN FormType = 'SharedDrive' THEN 'Shared Drive Access' WHEN FormType = 'SuspendAccess' THEN 'Suspend Access' WHEN FormType = 'TMChange'
                             THEN 'Team Manager Change Only' WHEN FormType = 'TransferIn' THEN 'Transfer In' WHEN FormType = 'TransferOut' THEN 'Transfer Out' END AS
                             FormName
     FROM         cte AS cte_1
option (maxrecursion 10000)
0
 
LVL 7

Author Comment

by:urir10
ID: 33488938
tried it already and get the same error.
0
 
LVL 7

Author Comment

by:urir10
ID: 33488941
i should add that this code works fine in Production server but not in Dev
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33488952
i.e. add a OPTION MAXRECURSION at the bottom.

It looks like one of the VIEWS used in the CTE (vw_TFAM_SLA_Times OR vw_TFAM_ALL) OR both is using recursion and it has looped up to 100 times.  It is sometimes symptomatic of a wrongly written CTE, that will never end even if given the option to go to 10000 recursions.
0
 
LVL 7

Author Comment

by:urir10
ID: 33488992
yes i added that and still didnt do it. i also added it to the View
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33489087
All it means is that the data in production avoids the error.
As an example, if your cte is trying to follow a link from member->referer, and you have accidentally set up A->B->C->A, it will keep following its tail and never finish.
Without looking at the CTE in the views, can't comment on much except generally.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 7

Author Comment

by:urir10
ID: 33489532
Thanks for the advice , i will recheck all views and functions and report back on monday as the stuff is at work.
0
 
LVL 7

Author Comment

by:urir10
ID: 33501466
i think i found the code that causes the error.
Below is one of the view i have and if i remove that line:
SUM(OneForm.dbo.[CalcMinutesSLA_TFAM](a.setdate, b.setdate)) AS SLA_in_Min

it then works fine
Before:

WITH cte2 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         cte2 AS a LEFT JOIN

                            cte2 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



After:

WITH cte2 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

     FROM         cte2 AS a LEFT JOIN

                            cte2 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

Open in new window

0
 
LVL 7

Author Comment

by:urir10
ID: 33501479
Here is the function CalcMinutesSLA_TFAM


ALTER FUNCTION [dbo].[CalcMinutesSLA_TFAM] (

 @startTime DATETIME,

 @endTime DATETIME

 ) 

returns int

as

begin

   declare @minutes int;

   

   

   declare @wdStartMins int, @wdLength int

   set @wdStartMins = 540; 

   set @wdLength = 480; 



   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 cteTable(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 cteTable

      where dateadd(mi, @wdStartMins, datediff(d, 0, st+1)) < @lwd

   )

    select @minutes = sum(datediff(mi, st, et))

   from cteTable 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

Open in new window

0
 
LVL 11

Expert Comment

by:tickett
ID: 33529249
Can you explain what your function is doing? It looks like it's working out the number of minutes between (A) and (B) subtracting holidays from your pre-defined table. Maybe there is a way to achieve this without cte/recursion.

I'm not sure what the rules are around nested cte...

L
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33530233
Try the code below - !! CAUTION !! - please test it thoroughly.

My guess is that CTE recursions are counted cumulatively for all recursive/embedded CTEs.  So if you have 3 levels of CTE and top level causes 2nd level to loop 50 times, 2nd level causes 3rd level to loop 80 times (80 days), you get 4000 recursions already without even looping on the top level.

Consider using while loops or other methods to reduce the number of CTE calls.
CREATE FUNCTION [dbo].[CalcMinutesSLA_TFAM] (

 @startTime DATETIME,

 @endTime DATETIME

 ) 

returns int

as

begin

	declare @minutes int;

	declare @startPlus1 datetime

	declare @endLess1 datetime

	-- work out the range for the full days in between

	select @startPlus1 = datediff(d, 0, @startTime+1),

			@endLess1 = datediff(d, 0, @endTime-1)

	-- check how many non-weekend days exist in this range

	select @minutes = 480 * (convert(int,@endLess1-@startPlus1)/7*5 + count(*))

	from master..spt_values

	where type='P' and number between 0 and datediff(d,

		@startPlus1+convert(int,@endLess1-@startPlus1)/7*7, @endLess1)

		and datename(dw, @startPlus1+number) != 'S'

	-- check how many non-weekend holidays exist in this range

	select @minutes = @minutes - count(*)

	from web_holidays

	where HolidayDate between @startPlus1 and @endless1

	  and left(datename(dw,HolidayDate),1) != 'S'

	-- adjust times for first/last days

	if datepart(hh,@startTime) < 9 set @startTime = datediff(dd,0,@startTime)+9.0/24

	if datepart(hh,@endTime) >= 17 set @endTime = datediff(dd,0,@endTime)+17.0/24

	if @startTime >= @endTime return coalesce(@minutes, 0);

	-- if start date is not a holiday and not a weekend, add minutes

	select @minutes = @minutes +

		datediff(mi, @startTime, datediff(dd,0,@startTime)+17.0/24)

	where not exists (

		select * from web_holidays

		where HolidayDate = datediff(dd,0,@startTime))

		and left(datename(dw,@startTime),1) != 'S'

	-- if end date is not a holiday and not a weekend, add minutes

	select @minutes = @minutes +

		datediff(mi, datediff(dd,0,@endTime)+9.0/24, @endTime)

	where not exists (

		select * from web_holidays

		where HolidayDate = datediff(dd,0,@endTime))

		and left(datename(dw,@endTime),1) != 'S'

	-- if start=end, we have counted the day twice, take 480 off

	select @minutes = @minutes - 480

	where not exists (

		select * from web_holidays

		where HolidayDate = datediff(dd,0,@endTime))

		and left(datename(dw,@endTime),1) != 'S'

		and datediff(d, @startTime, @endTime) = 0 -- same day

   return coalesce(@minutes, 0);

end

GO

Open in new window

0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33530241
This is how I read your original function
ALTER FUNCTION [dbo].[CalcMinutesSLA_TFAM] (

 @startTime DATETIME,

 @endTime DATETIME

 ) 

returns int

as

begin

   declare @minutes int;

   

   declare @wdStartMins int, @wdLength int

   set @wdStartMins = 540;  -- 9am

   set @wdLength = 480;  -- 8 hours, start+length=5pm



   declare @fwd datetime, @lwd datetime

   set @fwd = dateadd(mi, @wdStartMins, datediff(d, 0, @startTime)); -- 9am

   if (@fwd < @startTime) set @fwd = @startTime; -- start is later of 9am/@start

      

   set @lwd = dateadd(mi, @wdStartMins+@wdLength, datediff(d, 0, @endTime));

   if (@lwd > @endTime) set @lwd = @endTime; -- end is earlier of 5pm/@end

   

   -- this CTE sets up a day-by-day timetable by stepping through one day at a time.

   -- each day between start and end is one record 9am-5pm

   -- the first day starts: later of 9am/@start

   -- the last day ends: earlier of 5pm/@end (this can also be start day)

   ;with cteTable(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 cteTable

      where dateadd(mi, @wdStartMins, datediff(d, 0, st+1)) < @lwd

   )

   select @minutes = sum(datediff(mi, st, et))

   from cteTable C

   LEFT JOIN web_holidays H

	ON CONVERT(varchar,C.st,101) = H.HolidayDate   

   where left(datename(dw, st), 1) <> 'S' -- exclude weekends

   and st < et -- exclude negative ranges, only possible on first/last day

   and H.HolidayDate IS NULL -- exclude holidays

   ;

   

   return coalesce(@minutes, 0);

end

GO

Open in new window

0
 
LVL 7

Author Closing Comment

by:urir10
ID: 33535142
This one kinda put me on track, thanks
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

744 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

12 Experts available now in Live!

Get 1:1 Help Now