SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Sagar-636902
Sagar-636902
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 1119
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
Gift Peddie
Gift Peddie
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6340 Visits: 14456
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
Sagar-636902
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 1119
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
Gift Peddie
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6340 Visits: 14456
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
Sagar-636902
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 1119
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
Gift Peddie
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6340 Visits: 14456
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search