February 9, 2009 at 6:11 pm
Hi,
I had submitted the issue in BI section thought it is issue on query itself, i am getting error on stored proc only select all list form parameter. stored proc also ust CTE. not sure if it is caused by CTE, i had set the option to maximum as well and changed local temp to global temp. Is there limit of parameter list or any suggestions.
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?
February 10, 2009 at 12:49 am
Either you actually have a hierarchy of 100 levels or more, or you have a case of circular reference which is more likely.
N 56°04'39.16"
E 12°55'05.25"
February 12, 2009 at 8:45 pm
Hi, i changed cte to temp table now it works. there was a loop using cte.. i don't know why i create temp table according to cte it works
thanks.
sagar
February 13, 2009 at 10:28 am
Sagar,
Those CTEs are tricky.. I find that anything recursive is not so easy to debug.
Just curious, are you familiar with Sammy Hagar?
February 13, 2009 at 5:28 pm
no i am not
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply