We have recently migrated an application, and in the process we have migrated from SQL Server 2005 to SQL Server 2008 R2.
I have noticed that some SQL queries using CTE do not work anymore since.
I have tried simplifying the queries, but to no avail. Even this following simple query (which gets the top parent in a hierarchy) ends up with the error message hereunder :
DECLARE @Companies TABLE ( AccountId UNIQUEIDENTIFIER ,NAME VARCHAR(100) ,ParentAccountId UNIQUEIDENTIFIER ) INSERT INTO @Companies SELECT AccountId ,NAME ,ParentAccountId FROM AccountBase; WITH CTE AS ( SELECT * ,0 [Level] FROM @Companies UNION ALL SELECT CTE.accountid ,CTE.NAME ,cmp.ParentAccountId ,LEVEL + 1 FROM CTE INNER JOIN @Companies AS cmp ON CTE.ParentAccountId = cmp.AccountId WHERE cmp.ParentAccountId IS NOT NULL ) SELECT c.AccountId ,c.NAME ,c.ParentAccountId as TopParentAccountId FROM ( SELECT * ,MAX([Level]) OVER (PARTITION BY NAME) [MaxLevel] FROM CTE ) c WHERE MaxLevel = LEVEL
Error message (msg 530):
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
What could be possible reasons ?
Thanks in advance.