SqlHierarchyId.GetDescendant failed because its result is too big

by Casey Crookston   Last Updated October 09, 2019 22:06 PM - source

I don't think I have ever in my life googled an error message and come up blank... until now.

24006: SqlHierarchyId.GetDescendant failed because its result is too big.

This is happening inside a stored proc. It's huge ugly beast (that I didn't write) so I will try and show only the essentials.

    @parentNode HIERARCHYID
    ,@lastChild HIERARCHYID

SELECT @parentNode = TocNode FROM TableOfContents WHERE TocNodeLevel = @FooLevel
SELECT @lastChild = max(TocNode) FROM TableOfContents WHERE TocNode.GetAncestor(1) = @parentNode

INSERT INTO TableOfContents (TocNode...)
    @parentNode.GetDescendant(@lastChild, NULL) -- ERROR HERE
    blah blah blah

TableOfContents.ToCNode is a HIERARCHYID.

As there is only one instance of GetDescendant() in the entire SP, I assume it is happening on that line.

I hope this is enough to help?!? Here's the entire error message:

A .NET Framework error occurred during execution of user-defined routine or aggregate "hierarchyid": Microsoft.SqlServer.Types.HierarchyIdException: 24006: SqlHierarchyId.GetDescendant failed because its result is too big. Microsoft.SqlServer.Types.HierarchyIdException: at Microsoft.SqlServer.Types.OrdPath.AppendBits(UInt16& bitOffset, UInt64 x, UInt16 nBits) at Microsoft.SqlServer.Types.OrdPath.WriteOrd(UInt16& bitOffset, Int64 ord) at Microsoft.SqlServer.Types.XmlIdGenerator.GetNextID() at Microsoft.SqlServer.Types.SqlHierarchyId.GetDescendant(SqlHierarchyId child1, SqlHierarchyId child2)


HIERARCHYID does seem to have a length limit: 892. But it's read-only. If we end up going over this, are we just SOL?

Related Questions

Computed column from null column

Updated June 15, 2018 00:06 AM

creating index on big table(s)

Updated September 03, 2018 18:06 PM