3

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?

3

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.

  • So there is no debugging that can be done to see which file the error/type is coming from? Additionally, is there a way to take the working version of the type that I have and bring it into sql server under a different name (along with the associated types)? – soandos Feb 18 '15 at 14:09
  • @soandos Oh I am sure there is more debugging you can do, like with a real debugger, but I'm not the guy for that. And no, I don't think there is going to be an easy way for you to implement HierarchyId2. You need to fix your system, not waste time trying to avoid it. – Aaron Bertrand Feb 18 '15 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.