Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

error occur : maximum recursion 100 has exhusted before statement compelete (in SSRS) Expand / Collapse
Author
Message
Posted Sunday, February 8, 2009 5:30 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 9:44 AM
Points: 104, Visits: 1,029
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
Post #652500
Posted Sunday, February 8, 2009 7:35 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:22 AM
Points: 3,433, Visits: 14,430
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
Post #652515
Posted Sunday, February 8, 2009 8:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 9:44 AM
Points: 104, Visits: 1,029
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
Post #652524
Posted Monday, February 9, 2009 5:43 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:22 AM
Points: 3,433, Visits: 14,430
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
Post #652725
Posted Monday, February 9, 2009 5:33 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 9:44 AM
Points: 104, Visits: 1,029
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
Post #653337
Posted Monday, February 9, 2009 6:28 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:22 AM
Points: 3,433, Visits: 14,430
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
Post #653358
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse