Microsoft SQL 2008 introduced the hierarchyid datatype helping with child/parent relationships. However this causes an issue if you try to select its data via ColdFusion’s Microsoft SQL Server driver. This breaks in ColdFusion 9.0.1 and I assume prior versions.

Example Table:

CREATE TABLE Org_T1
(
EmployeeId hierarchyid PRIMARY KEY,
EmployeeName nvarchar(50)
)

Example SQL Call:

SELECT * FROM Org_T1

Now first of all you should try to avoid “SELECT *” when possible anyway to increase efficiency. Otherwise the table columns are looked up before querying for results, adding an extra process step.

The way around this would be to list the columns you actually need in the select statement. If you need the data from the “EmployeeId” column you could append the “.ToString()” function to the column name. Example:

SELECT EmployeeId.ToString(), EmployeeName
FROM Org_T1

This will in effect change what data you receive however. The raw data looks something like “0xf0”. The result of the ToString() method looks something like “\1\” and I believe will be of varchar type.

There is a bug entry with Adobe here: http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html#bugId=83590 . Feel free to go vote on this issue to increase its visibility with the ColdFusion team.