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)
INNER JOIN @TBLManagers mg
ON mg.ManagerId = pm.ManagerId
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?