I have a recursive query that returns an error when I run it; in other databases (with more data) I have not the problem. In my case this query returns 2 colums (ID_PARENT and ID_CHILD) doing a recursion because my tree can have more than one level, bit I wanna have only "direct" parent.

NOTE: I tried to put OPTION (MAXRECURSION 0) at the end of the query, but with no luck. The following query is only a part of the entire query, I tried to put OPTION only at the end of the "big query" having a continous running query, but no errors displayed.

Error have in SQL Server:

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

The query is the following:

WITH q
     AS (SELECT ID_ITEM,
                ID_ITEM AS ID_ITEM_ANCESTOR
         FROM   ITEMS_TABLE i
                JOIN ITEMS_TYPES_TABLE itt
                  ON itt.ID_ITEM_TYPE = i.ID_ITEM_TYPE
         UNION ALL
         SELECT i.ID_ITEM,
                q.ID_ITEM_ANCESTOR
         FROM   q
                JOIN ITEMS_TABLE i
                  ON i.ID_ITEM_PADRE = q.ID_ITEM
                JOIN ITEMS_TYPES_TABLE itt
                  ON itt.ID_ITEM_TYPE = i.ID_ITEM_TYPE)
SELECT ID_ITEM          AS ID_CHILD,
       ID_ITEM_ANCESTOR AS ID_PARENT
FROM   q 

I need a suggestion to re-write this query to avoid the error of recursion and see the data, that are few.