I’m basing my current project around SQL 2008’s hierarchyid data type, which is completely new to me. One frustrating issue I have found is the inability to edit any hierarchyid type’s data as a string inside the Microsoft SQL Server Management Studio’s edit feature. Granted I can control the data via the New Query feature, but I tend to use the interactive editor for quick and easy access to my data during the development process.

Take the following example:

Notice the hierarchyid data is converted into a string to be shown. However if you where to try and update the current value of ‘/1/’ to ‘/1/1/’ or update the current NULL value to ‘/1/1/’ then you would receive an error stating “Invalid cast from ‘System.String’ to ‘Microsoft.SqlServer.Types.SqlHierarchyId'”.

I’m not sure what data would even be acceptable in the field. I tried entering ‘0x5AC0’ for the NULL data and still received an error.

So from what I can see is this data type is unchangeable inside the editor.

The workaround for this would be to run the following TSQL:

UPDATE Table1
SET Question_Hierarchy_ID = '/1/1/'
WHERE ID=1