urir10
asked on
The statement terminated. The maximum recursion 100 has been exhausted before statement completion
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?
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
ASKER
tried it already and get the same error.
ASKER
i should add that this code works fine in Production server but not in Dev
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.
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.
ASKER
yes i added that and still didnt do it. i also added it to the View
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.
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.
ASKER
Thanks for the advice , i will recheck all views and functions and report back on monday as the stuff is at work.
ASKER
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.[CalcMinut esSLA_TFAM ](a.setdat e, b.setdate)) AS SLA_in_Min
it then works fine
Below is one of the view i have and if i remove that line:
SUM(OneForm.dbo.[CalcMinut
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
ASKER
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
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
I'm not sure what the rules are around nested cte...
L
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
This one kinda put me on track, thanks
WITH cte AS (SELECT dbo.vw_TFAM_ALL.TicketID, dbo.vw_TFAM_ALL.CC, dbo.vw_TFAM_ALL.FormType, dbo.vw_TFAM_ALL.SubmittedO
dbo.vw_TFAM_ALL.AcknBy, dbo.vw_TFAM_ALL.AcknOn, dbo.vw_TFAM_ALL.SubmittedB
dbo.vw_TFAM_ALL.isSubmitte
dbo.vw_TFAM_ALL.Business, dbo.vw_TFAM_ALL.ReqType, CASE WHEN [OneForm].[dbo].vw_TFAM_AL
[OneForm].[dbo].vw_TFAM_AL
END AS SLA_Minutes, CASE WHEN [OneForm].[dbo].vw_TFAM_AL
[OneForm].[dbo].vw_TFAM_AL
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.tick
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)