Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

MSSQL - handling "hierarchyid" data type?

Jeto's profile image Jeto posted 5 years ago in Creating a connection Permalink
So I've started using HeidiSQL to access our MSSQL (SQL Server) database at work, as I find it a lot lighter and more intuitive than the Microsoft tool.

However, I noticed the data type hierarchyid is not handled correctly. Some weird asian characters are displayed instead of the actual /1/2/ or whatever value.

Is there any plan to fix this?

Thanks!
Jeto's profile image Jeto posted 5 years ago Permalink
Oops, I meant to post this in "Feature discussion" (or General)...
ansgar's profile image ansgar posted 5 years ago Permalink
Yes, I can add support for data types but I need to know how to display these. For example as text or integers, date/time, etc.

To what other data type is that hierarchyid comparable?
Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
Jeto's profile image Jeto posted 5 years ago Permalink
https://msdn.microsoft.com/en-us/library/bb677290.aspx

It's a bit special... basically it's an hexadecimal number but can be displayed like /1/, /1/1/, /1/2/, /2/1/, etc. In SQL Server you can use ToString() to view this representation, which is the most human-readable.
ansgar's profile image ansgar posted 5 years ago Permalink
Oh, that sounds like HeidiSQL would need some entirely new grid editor for editing such values.

Or, to make it easier, just display such columns by applying ToString() before.
Code modification/commit from ansgar.becker, 5 years ago, revision 4900
Add support for MSSQL data type HIERARCHYID. See http://www.heidisql.com/forum.php?t=17575
ansgar's profile image ansgar posted 5 years ago Permalink
Done in r4900. Such columns should be detected now as HIERARCHYID in the table editor. The date type group is "Other", just like "CURSOR", "SQL_VARIANT" and some others.

The conversion with TOSTRING() did not work here. ("ToString is not detected as an internal function"). So I followed the documentation which says that CAST(x AS NVARCHAR(y)) does the same, implicitly.

Please update to the latest build and test.
Jeto's profile image Jeto posted 5 years ago Permalink
Wow, that was fast. Will try it out tomorrow and let you know.

Thanks!

Please login to leave a reply, or register at first.