title: CTE with multiple UNION ALL, The maximum recursion 100 has been exhausted before statement completion
body: <p>The following code produces: The statement terminated. The maximum recursion 100 has been exhausted before statement completion.</p>
  
  <p>I'm trying to build a list with all parents and childs. ArtikelCEId = 7 is hardcoded as an example.</p>
  
  <p>Running the CTE with only 1 UNION ALL (find parents or childs) works perfectly.</p>
  
  <p>The same kind of coding is described in: <a href="http://sqlmag.com/t-sql/ctes-multiple-recursive-members" rel="nofollow">http://sqlmag.com/t-sql/ctes-multiple-recursive-members</a></p>
  
  <pre><code>create table Article(
      ArtikelCEId int,
      VervangenDoorArtikelCEId int
  ) 
  
  insert into Article (ArtikelCEId, VervangenDoorArtikelCEId ) values (1, 2)
  insert into Article (ArtikelCEId, VervangenDoorArtikelCEId ) values (2, null)
  insert into Article (ArtikelCEId, VervangenDoorArtikelCEId )  values (3, null)
  insert into Article (ArtikelCEId, VervangenDoorArtikelCEId ) values (5, 7)
  insert into Article (ArtikelCEId, VervangenDoorArtikelCEId ) values (7, 8)
  insert into Article (ArtikelCEId, VervangenDoorArtikelCEId ) values (8, null)
  insert into Article (ArtikelCEId, VervangenDoorArtikelCEId ) values (9, null)
  insert into Article (ArtikelCEId, VervangenDoorArtikelCEId ) values (10, null)
  
  ;                                       
  WITH ArtikelCE_CTE(ArtikelCEId, VervangenDoorArtikelCEId) AS 
  ( 
    SELECT a.ArtikelCEId, a.VervangenDoorArtikelCEId
    from Article a 
      where a.ArtikelCEId = 7
  
      /*find parents */
    UNION ALL
    SELECT b.ArtikelCEId, b.VervangenDoorArtikelCEId
    FROM ArtikelCE_CTE acteP 
    INNER JOIN Article b 
      ON b.ArtikelCeId = acteP.VervangenDoorArtikelCEId  
  
    /*find childs */
    UNION ALL
    SELECT c.ArtikelCEId, c.VervangenDoorArtikelCEId
    FROM ArtikelCE_CTE acteC 
    INNER JOIN Article c 
      ON c.VervangenDoorArtikelCEId = acteC.ArtikelCeId    
    where c.VervangenDoorArtikelCEId is not null
  
  )
  
  
  SELECT  ArtikelCeId, VervangenDoorArtikelCEId FROM ArtikelCE_CTE
  </code></pre>