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.
DECLARE @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...) SELECT @parentNode.GetDescendant(@lastChild, NULL) -- ERROR HERE ... FROM blah blah blah
TableOfContents.ToCNode is a
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?