error occur : maximum recursion 100 has exhusted before statement compelete (in SSRS)

Question

error occur : maximum recursion 100 has exhusted before statement compelete (in SSRS)

Sagar-636902

SSCommitted

Points: 1651
More actions
February 8, 2009 at 5:30 pm

#383891

Hi,
i am having problem running report in production , in management studio when i execute it works fine but when i run the proc using ssrs i throw error maximum recursion 100 has exhusted before statement compelete, and this error occurs only when i select 2009 parameter if i select the prior on it works fine in ssrs. don't know what happen. is there problem using CTE in ssrs, i know there is CTE in report i also set the maximum option still say same error but with the maximum recursion that i set.
below is CTE code
;WITH PORFnMgr (ManagerId, Manager, ResourceId, Resource)
--(QCount, ManagerId, Manager, DepartmentDescription, ProductLine, Program, Project, JobType,
--SubJobType, GLFunction, BTIName, ResourceId, ResourceName)
AS
(
SELECT
DISTINCT mg.ManagerId
,pm.Manager
,pm.ResourceId
,pm.Resource
FROM
#PORByMgr pm
INNER JOIN @TBLManagers mg
ON mg.ManagerId = pm.ManagerId
UNION ALL
SELECT
por.ManagerId
,por.Manager
,por.ResourceId
,por.Resource
FROM
#PORByMgr por
INNER JOIN PORFnMgr po
ON po.ResourceId = por.ManagerId
)
SELECT DISTINCT ManagerId INTO #tempMgrs FROM PORFnMgr
option (maxrecursion 2000)
Any body have idea, please help me figuer out?
thanks
Sagar

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

I can tell you maximum recursion is not your problem, you are running code in a virtual view which is using many local temp tables SSRS rejects most temp table code so you need to convert your temp tables to either View or regular temp table.

To fix your current code try ## global temp table remember to drop all or try table variables.

Kind regards,
Gift Peddie

Sagar-636902 SSCommitted Points: 1651 More actions · Answer 2

Thanks for ther reply Gift Peddie, that could be the reason, i know there is lots of local temp table, and is there number of limitation that i can have when using ssrs and i should be aware of?

Thanks

Sagar

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

In general SSRS rejects local temp table code based on the duration of the operation, I have an old thread at this site where others posted alternatives I will look for it but global temp table also works.

Kind regards,
Gift Peddie

Sagar-636902 SSCommitted Points: 1651 More actions · Answer 4

I tried with global temp table still error, actually it has no issue with ssrs, it throw an error in management studio as well only when i select all the list from the parameter. it was exact same error. is there number of limitation of parameter list in any proc? not sure what could be the issue.

thanks

Sagar

Gift Peddie SSC Guru Points: 73570 More actions · Answer 5

If the code is not running in SMS then it is time to redesign the Report by converting the CTE and temp tables to Views.

Kind regards,
Gift Peddie