Mark,

Excellent solution. It works!

FYI, nesting loops is needed to list the offending data to send back to the other team so that they can clean it up at the source.

Yep, NodeIDs are repeated here as examples to show infinite loops in 0, 1, or 2 steps. These loops are actually easier to catch and removed. There are...

Old Hand,

Your 2nd CTE still blew up with the following sample data:

insert into NodeItem

select 1 NodeID, -1 ParentNodeID -- 1st root

union all select 2 , NULL -- the 2nd root

union...

Old Hand,

Your CTE will not work in my instance because Root Nodes may not have ParentNodeID = 0. As I said, there might be many root nodes.

Thanks...

I should have clarified that:

(1) There may be many root nodes (root = dead end or no more parent entry found);

(2) Not all paths will have the same depth (steps);

(3)...

EIJ,

No, the following syntax didn't work:

    FROM ColumnName

    WHERE CONTAINS(FulltextCatelogueName, '"*men*"')

I tried the following, and it didn't return expected result, either.

    FROM TableName

    WHERE CONTAINS(ColumnName, '"*men*"')

Thanks.

Larry

Hi, Amit,

I have been wondering about the same issue with a table that has over 10 million rows.  A Full-Text Index was built to facilitate free text search on one varchar(50) column.  How surprised...

You're probably working with MX which automatically escapes single quotes within <CFQUERY> tag (by adding another single quote).

Larry

I got Community Updates in my hotmail account.  After the update whenever I clicked on a link in the mail, I got Access Deny page.  For whatever reason the new...

Thanks, Andy for the discussion.

I tried the two index approach - id1 on A(a1, a2), and id2 on B(b1, b2) - on the query:

Select A.a3, B.b3

from A inner join...

Would some one points me to the right direction or links?