1

I am having issues when I try and generate hierarchyid in my application. Specifically, I cannot seem to have more than 16 children per node. Here is the code that I am trying to use for my stored proc (this is for a test table that just has an hid field, and a content field:

create PROC testAdd (@parentid hierarchyid, @content varchar(10)) 
AS
BEGIN
    DECLARE
       @parentNode hierarchyid , 
       @lc hierarchyid;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRANSACTION;
    SELECT @lc = MAX (hid)
      FROM testing
      WHERE hid.GetAncestor (1) = @parentid;

    INSERT INTO testing (hid, content) 
    VALUES ( @parentid.GetDescendant(@lc, NULL),
    @content) ;
    COMMIT;
END;

Here is how I get an error:

  1. Run INSERT INTO testing (hid , content) VALUES (hierarchyid::GetRoot () , 'a') ;
  2. Run EXEC testAdd '/' , 'a'; 16 times.

The error I get looks like: enter image description here

Additionally, I attempted to run the second code snippet from this answer, and it broke immediately.

What could be going wrong?

Update: I have already uninstalled VS 2015, and the CLR data types for sql server 2014 and replaced them with an older version to no avail.

Community
  • 1
  • 1
soandos
  • 4,978
  • 13
  • 62
  • 96
  • FWIW I can run the EXEC testAdd more than 16 times with no error. Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Developer Edition (64-bit) on Windows NT 6.3 (Build 9600: ) – Clint Good Dec 22 '14 at 04:56
  • @ClintGood I think it has something to to with the fact that I installed VS 2015 preview. What is the version of the Types assembly? – soandos Dec 22 '14 at 05:02
  • microsoft.sqlserver.types, version=12.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil – Clint Good Dec 24 '14 at 04:31
  • @ClintGood is there a way to see what assembly the hierarchyid type is coming from? – soandos Dec 24 '14 at 04:39
  • I just ran select * from sys.assemblies – Clint Good Dec 24 '14 at 04:40
  • @ClintGood is there anything else that can mess with the type (I get the exact same thing as you) – soandos Dec 24 '14 at 04:42
  • I would try each part individually. `set @parentid = '/' select @lc = max(hid)...` and see which line it is dying on. – Clint Good Dec 24 '14 at 04:47
  • @ClintGood The simpler sample (from [here](http://stackoverflow.com/questions/2840476/should-i-worry-about-running-out-of-hierarchyids/2840507#2840507) second code snipped) shows it breaks on ToString – soandos Dec 24 '14 at 04:56
  • @ClintGood https://connect.microsoft.com/SQLServer/feedback/details/1063935/visual-studio-2015-breaks-hierarchyid-clr-type – soandos Dec 24 '14 at 14:47

0 Answers0