Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2007
    Posts
    1

    Unanswered: T-SQL Transitive Closure

    Hello,

    I need to find a decision for connected elements in graph, but when i trying an examle from "Microsoft.SQL.Server.2005.T-SQL.Querying" an error message was occured:

    "Msg 530, Level 16, State 1, Line 1
    The statement terminated. The maximum recursion 100 has been exhausted before statement completion."

    After it i put "OPTION (MAXRECURSION 10000), " but result was the same.

    Please help me!


    WITH BOMTC
    AS
    (
    -- Return all first-level containment relationships
    SELECT assemblyid, partid
    FROM dbo.BOM
    WHERE assemblyid IS NOT NULL

    UNION ALL
    -- Return next-level containment relationships
    SELECT P.assemblyid, C.partid
    FROM BOMTC AS P
    JOIN dbo.BOM AS C
    ON C.assemblyid = P.partid
    )
    -- Return distinct pairs that have
    -- transitive containment relationships
    SELECT DISTINCT assemblyid, partid
    FROM BOMTC;

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Fancy telling me what this query is supposed to achieve?
    It seems a bit like 2 sides of a triangle!

    Humour me
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I beleive OPTION (MAXRECURSION 0) will let it go on forever. Make sure that your temination condition eventually fires.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •