2

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.

  • Maybe something changed in your data? You can increase the limit with MAXRECURSION option – James Z Oct 16 '15 at 9:45
  • The concerned table has not be changed.. much ! I.e. some obsolete fields were removed, but it is all. i have indeed tried to change the MAXRECURSION, up to the limit and it did not work either. – salcoin Oct 16 '15 at 9:47
  • 2
    You need to increase the maxrecursion (default is 100) or set no limit with OPTION (MAXRECURSION 0) – ypercubeᵀᴹ Oct 16 '15 at 9:50
  • Can you check if this returns any rows? SELECT * FROM AccountBase WHERE ParentAccountId = AccountId ; – ypercubeᵀᴹ Oct 16 '15 at 9:52
  • @ypercube No, it does not return anything (and it should not). – salcoin Oct 16 '15 at 9:54
3

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.

  • In my case, as we discussed in the question comments, it was indeed bad data. Thanks again :) – salcoin Nov 22 '16 at 10:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.