InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 01:40:24 GMT20http://www.sqlservercentral.com/Forums/Topic1078252-2571-1.aspxI suspect that one of your records in the User_Master table has the created_by set to the same value as the user_id.This sample works fine for me, but uncommenting the 1st value in the insert and commenting out the second causes your error.[code="sql"]CREATE TABLE #User_Master ( user_id INT , created_by INT);INSERT #User_Master VALUES --(1195, 1195) (1195, 1196) , (1234, 1195) , (5678, 1234) ;with child_user(user_id, created_by) as ( Select user_id, created_by from #User_Master where user_id= 1195 union all select ro.user_id, ro.created_by from #User_Master ro join child_user cv on cv.user_id = ro.Created_by )select user_id, created_by from child_user CV DROP TABLE #User_Master[/code]Mon, 31 Oct 2011 20:23:51 GMTToby Harmanhttp://www.sqlservercentral.com/Forums/Topic1078252-2571-1.aspxI suspect that what you've got is an infinite self-join in the way you've written the CTE. I'd need to see the definition of the tables and the data in them before I could be certain, but it looks like you're joining the first value to itself, which will create an infinite loop.Tue, 15 Mar 2011 06:33:31 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1078252-2571-1.aspxDear All,Can any one help me to solve the below problem?Thanks in advance.I am using Common Table Expression (CTE) in sql server 2008 to fetch the hierarchy of the users. ;with child_user(user_id, created_by) as ( Select user_id, created_by from User_MAster where user_id= 1195union all select ro.user_id, ro.created_byfrom User_Master rojoin child_user cv on cv.user_id = ro.Created_by )select user_id, created_by from child_user CVWhen I executing the above query, I am getting an error message as displayed "Msg 530, Level 16, State 1, Line 1The statement terminated. The maximum recursion 100 has been exhausted before statement completion."After that I tried include the Option as below ;with child_user(user_id, created_by) as ( Select user_id, created_by from User_MAster where user_id= 1195union all select ro.user_id, ro.created_byfrom User_Master rojoin child_user cv on cv.user_id = ro.Created_by )select user_id, created_by from child_user CVoption (maxrecursion 32765);Even now I am getting same error message with "....maximum recursion 32765...."I had only two records in my master table related to the above user_id.BUt I am geting duplicate rows (32765 records).Please help me to solve the same.Thanks & Regards,Krishna Kumar PTue, 15 Mar 2011 05:53:22 GMTkrishnakumar.palchamy