hierarchical select

hello,
i have a hierarchical select
something like this
with temp_containerCollect (id) as (
select c.id
from LOGI_T_CONT4REFCOLLECT c
where c.parent_id is null AND c.isActive like 'Y'
/~filter_refuseCollection: AND c.refusecollection_id = {filter_refuseCollection} ~/
union all
select st.id from LOGI_T_CONT4REFCOLLECT st
inner join temp_containerCollect tst on st.parent_id = tst.id
where st.isActive like 'Y'
) select * from temp_containerCollect t
sometimes i get error with message The statement terminated. The maximum
recursion 100 has been exhausted before statement completion
i understand the message
i know solution with using hint OPTION (MAXRECURSION 2) but i don't wanna
use it
i'd like to set max recursion option globally for all hierarchical selects
so the question is: how to set max recursion option globally for database
instance?
thanks for all
TVHi Tomas
"Tomas Vojtech" wrote:
> hello,
> i have a hierarchical select
> something like this
> with temp_containerCollect (id) as (
> select c.id
> from LOGI_T_CONT4REFCOLLECT c
> where c.parent_id is null AND c.isActive like 'Y'
> /~filter_refuseCollection: AND c.refusecollection_id => {filter_refuseCollection} ~/
> union all
> select st.id from LOGI_T_CONT4REFCOLLECT st
> inner join temp_containerCollect tst on st.parent_id = tst.id
> where st.isActive like 'Y'
> ) select * from temp_containerCollect t
> sometimes i get error with message The statement terminated. The maximum
> recursion 100 has been exhausted before statement completion
> i understand the message
> i know solution with using hint OPTION (MAXRECURSION 2) but i don't wanna
> use it
> i'd like to set max recursion option globally for all hierarchical selects
> so the question is: how to set max recursion option globally for database
> instance?
> thanks for all
> TV
>
Although not quite what you asked the following suggestion has been logged at
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124653
where you can vote. If necessary you could add you specific request as well.
John