Sql-server – SQL query using CTE not working after database migration

ctesql serversql-server-2008-r2

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 :

Code:

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.

Best Answer

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

The obvious reason for this error is that the query needed to do recursion of more than 100 levels. So you can try increasing the default of 100 to higher (say 1000) or to no limit with OPTION (MAXRECURSION 0).

Another possibility is to have "bad data" (i.e. cycles in the id - parent_id adjacency list relationship) and not well written recursive CTE that falls into an infinite loop.

For the specific query, a cycle on the id - parent ids, say (AccountId, ParentAccountId) with values (1,2), (2,3), (3,1) will cause an infinite recursion loop.

I suggest you check the data in the table for cycles in the id - parent_id relationship and correct the query to avoid infinite recursion in any case this happens again.

Related Question