November 17, 2011 at 1:48 am
Hi Experts,
One of the stored procedure is failing with displaying below error.Till now it was runnign fine.
Pls suggest
The statement terminated. The maximum recursion 100 has been exhausted before statement completion. [SQLSTATE 42000] (Error 530).
--Ganga
November 17, 2011 at 2:16 am
Gangadhara MS (11/17/2011)
Hi Experts,One of the stored procedure is failing with displaying below error.Till now it was runnign fine.
Pls suggest
The statement terminated. The maximum recursion 100 has been exhausted before statement completion. [SQLSTATE 42000] (Error 530).
--Ganga
The stored procedure contains a recursive CTE; the number of recursions + the anchor exceeds 100, the default. The number of recursions is capped by the query option MAXRECURSION.
If the stored procedure has successfully executed over a reasonable period of time then I'd recommend that you raise MAXRECURSION to a sensible figure, say 200, and try the query again. It's quite possible that the changing shape of your data has pushed up the number of recursions, in which case you will probably have fixed the issue. It's also possible that a rogue data element has pushed the rCTE into an endless loop, in which case the stored procedure will fail with the same error.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 22, 2011 at 1:30 am
Hi All,
this is the query which is causing this error.
CREATE PROCEDURE [dbo].[TotalChargeByPosition] (
@Position_ID int,
@FirstOfMonth datetime,
@AllBelow int = 1,
@Charge money OUTPUT
)
AS
BEGIN
WITH DirectReports(Employee_ID, Parent_ID, PositionName, Position_ID, Team_ID) AS
(
SELECT Employee_ID, Parent_ID, PositionName, ID Position_ID, Team_ID
FROM Position
WHERE ID = @Position_ID -- start from this position
UNION ALL
SELECT e.Employee_ID, e.Parent_ID, e.PositionName, e.ID Position_ID, e.Team_ID Team_ID
FROM Position e
INNER JOIN DirectReports d
ON e.Parent_ID = d.Position_ID
)
SELECT distinct DirectReports.Employee_ID, E.Name, Position_ID, DirectReports.PositionName
into #tmp1
FROM DirectReports
LEFT OUTER JOIN emp E on E.ID = DirectReports.Employee_ID
ORDER BY E.Name
--OPTION (MAXRECURSION 1000)
-- only own cost
IF @AllBelow = 0 BEGIN
delete #tmp1
where Position_ID <> @Position_ID
END
set @Charge = 0
if exists (select * from tr_Bill
where DatePeriodStart = @FirstOfMonth
and User_Id in (select Employee_ID from #tmp1)
) BEGIN
select @Charge = sum(Charge)
from tr_Bill
where DatePeriodStart = @FirstOfMonth
and User_Id in (select Employee_ID from #tmp1)
group by DatePeriodStart
END
November 22, 2011 at 1:38 am
So, what values have you tried for MAXRECURSION, and what was the effect?
There is a method you can use to restrict the number of recursions to an absolute number without raising an error if MAXRECURSION fails.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 22, 2011 at 2:48 am
hi
i have used upto 32200 for max recursion but still same error. please suggest me some other method.
November 22, 2011 at 3:03 am
Gangadhara MS (11/22/2011)
hii have used upto 32200 for max recursion but still same error. please suggest me some other method.
Unless you're working for Microsoft, you're unlikely to have a hierarchy this deep - which means that you have an error in your data somewhere. You need to find and fix that error. Meantime, here's a means of self-limiting the number of iterations performed by your rCTE:
;WITH DirectReports(NodeID, Employee_ID, Parent_ID, PositionName, Position_ID, Team_ID) AS
(
SELECT NodeID = 1,
Employee_ID,
Parent_ID,
PositionName,
ID Position_ID,
Team_ID
FROM Position
WHERE ID = @Position_ID -- start from this position
UNION ALL
SELECT
NodeID = d.NodeID + 1,
e.Employee_ID,
e.Parent_ID,
e.PositionName,
e.ID Position_ID,
e.Team_ID Team_ID
FROM Position e
INNER JOIN DirectReports d
ON e.Parent_ID = d.Position_ID
WHERE d.NodeID <= 200
)
SELECT distinct DirectReports.Employee_ID, E.Name, Position_ID, DirectReports.PositionName
into #tmp1
FROM DirectReports
LEFT OUTER JOIN emp E on E.ID = DirectReports.Employee_ID
ORDER BY E.Name
--OPTION (MAXRECURSION 1000)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 23, 2011 at 1:51 am
NodeiD limiting also didn't worked for me.
Actually another SP is calling this sp inside the cursor so not able to find the exact culprit.
Something can be done here ?
November 23, 2011 at 2:51 am
in this query there is a problem now
WITH DirectReports(Employee_ID, Parent_ID, PositionName, Position_ID, Team_ID) AS
(
SELECT Employee_ID, Parent_ID, PositionName, ID Position_ID, Team_ID
FROM Position
--WHERE ID = @Position_ID -- start from this position
UNION ALL
SELECT e.Employee_ID, e.Parent_ID, e.PositionName, e.ID Position_ID, e.Team_ID Team_ID
FROM Position e
INNER JOIN DirectReports d
ON e.Parent_ID = d.Position_ID
)
SELECT distinct DirectReports.Employee_ID, E.Name, Position_ID, DirectReports.PositionName
--into #tmp1
FROM DirectReports
LEFT OUTER JOIN emp E on E.ID = DirectReports.Employee_ID
ORDER BY E.Name
November 23, 2011 at 5:47 am
Gangadhara MS (11/23/2011)
NodeiD limiting also didn't worked for me....
NodeID limiting works well providing the rCTE has a single anchor. If it didn't work then it was probably incorrectly written. What message did you get when you added NodeID to your query?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 23, 2011 at 5:51 am
Gangadhara MS (11/23/2011)
in this query there is a problem nowWITH DirectReports(Employee_ID, Parent_ID, PositionName, Position_ID, Team_ID) AS
(
SELECT Employee_ID, Parent_ID, PositionName, ID Position_ID, Team_ID
FROM Position
--WHERE ID = @Position_ID -- start from this position
UNION ALL
SELECT e.Employee_ID, e.Parent_ID, e.PositionName, e.ID Position_ID, e.Team_ID Team_ID
FROM Position e
INNER JOIN DirectReports d
ON e.Parent_ID = d.Position_ID
)
SELECT distinct DirectReports.Employee_ID, E.Name, Position_ID, DirectReports.PositionName
--into #tmp1
FROM DirectReports
LEFT OUTER JOIN emp E on E.ID = DirectReports.Employee_ID
ORDER BY E.Name
You've commented out the WHERE clause of the anchor part of the rCTE. If you were to run this query with unlimited recursions, it would hammer your server - which would probably raise an error related to exhausted resources.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 23, 2011 at 5:56 am
Gangadhara MS (11/23/2011)
NodeiD limiting also didn't worked for me.Actually another SP is calling this sp inside the cursor so not able to find the exact culprit.
Something can be done here ?
Something can almost always be done. The quality of the solution provided to you will be proportional to the quality of the information about the problem. What can you share?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 23, 2011 at 10:05 pm
Thanks for the reply.
I have got a solution to this but adding additional conditional with where clause,it worked out for me.
Thanks.
Ganga
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply