SQL query using CTE not working after database migration
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.