Sql-server – A workaround for restoring HierarchyId in SQL server 2014

debugginghierarchysql serversql server 2014

After installing VS 2015 CTP 4, HierarchyID stopped working in SQL Server 2014. This is the bug listed here.

I am looking for a workaround.

To that end, I have tried reinstalling everything associated with SQL Server 2014 and Visual Studio 2015 CTP.

Additionally, I noticed the following (before the reinstall):

enter image description here

After re-installing, I now have version 12.0.2000.8 installed, and its 6.80 MB.

Additionally, I know that I have a working version of the SQL Server type assembly as I can run the following C# code without error:

        var root = SqlHierarchyId.Parse("/");
        var parent = SqlHierarchyId.Parse("/1/");
        var child = SqlHierarchyId.Parse("/1/1/");
        Console.WriteLine(child.ToString());

While in SQL, the following errors:

    declare @Root hierarchyid = '/',
    @Parent hierarchyid = '/1/',
    @Child hierarchyid = '/1/1/';

    print @Root.ToString();
    print @Parent.ToString();
    print @Child.ToString(); -- Throws exception

Is there a way to force SQL Server 2014 to use a particular assembly for this type? Additionally, if I could track down the actual source of the file that is the source of the error I would have a chance at restoring that file/set of files. Is there a way to get the error message to show the file that it originated from?

Best Answer

I am looking for a workaround.

For me, based on conversations I've seen about this elsewhere, you have four options:

  1. Perform a system restore to a point prior to the Visual Studio CTP installation.

  2. Wait for Connect item #1063935 to be addressed - it may be in a future Cumulative Update or Service Pack for SQL Server, or it may be in a patch or some kind of update for the Visual Studio preview. It is not addressed explicitly in Cumulative Update #6, but applying that can't hurt to try.

  3. Back up your databases somewhere, and remove everything Visual Studio, .net & SQL Server related (the hard way, not through the Control Panel), and install SQL Server again.

  4. If 3. doesn't work, back up your databases somewhere, and nuke & pave your OS.

If 1. is not possible, then you need to take further action in the future to prevent having to do these other much harder things:

  • Either don't install Visual Studio, SQL Server etc. CTP or preview versions on your critical / primary workstation (virtual machines are a much better target for this sort of thing); or,
  • start taking better system backups so that if you do decide to install such things you can recover.

This can be an expensive lesson that, sadly, most people learn the hard way.

Related Question