We are working on SQL 2008 for a new project with the intent to leverage the
data types, in this case especially the
HIERARCHYID data type.
One of the stored procedures we created had a parameter of type
The code that calls the stored procedure was something like this.
SqlHierarchyId nodeValue = GetNodeValueFromSomewhere();
var cmd =new SqlCommand("usp_EnableNode");
cmd.CommandType = CommandType.StoredProcedure;
var nodeParam = myCommand.Parameters.Add("@Node", SqlDbType.SqlHierarchyId);
nodeParam.Value = nodeValue;
var enableParam = myCommand.Parameters.Add("@Enable", SqlDbType.SqlBit);
enableParam.Value = true;
When running this code we were getting the error "UdtTypeName property must be set for UDT parameters."
HIERARCHYID data type is a .NET type in SQL 2008 and we had not played with .NET
types in SQL 2005 yet, we scratched our heads for a few minutes.
We tried both:
nodeParam.UdtTypeName = typeof(SqlHierarchyId).Name;
nodeParam.UdtTypeName = typeof(SqlHierarchyId).FullName;
But the solution was much simpler:
nodeParam.UdtTypeName = "HierarchyId";
This should be needed for the new
GEOGRAPHY data types as well.
Maybe this helps someone else.
06-11-2008 3:38 PM