Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
UdtTypeName and .NET data types in SQL

We are working on SQL 2008 for a new project with the intent to leverage the new data types, in this case especially the HIERARCHYID data type. One of the stored procedures we created had a parameter of type HIERARCHYID.

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;
cmd.ExecuteNonQuery();

When running this code we were getting the error "UdtTypeName property must be set for UDT parameters." Since the 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; 

And

nodeParam.UdtTypeName = typeof(SqlHierarchyId).FullName; 

But the solution was much simpler:

nodeParam.UdtTypeName = "HierarchyId";

This should be needed for the new GEOMETRY and GEOGRAPHY data types as well. Maybe this helps someone else.


Posted 06-11-2008 3:38 PM by sergiopereira

[Advertisement]

Comments

Bala wrote re: UdtTypeName and .NET data types in SQL
on 09-09-2008 12:16 AM

But the keyword "SqlHierarchyId" doesn't seem appear in my VS 2008 IDE. I am having VS 2008 RTM version.

sergiopereira wrote re: UdtTypeName and .NET data types in SQL
on 09-09-2008 9:59 AM

@Bala,

SqlHierarchyId is not a keyword, it's just a type name. To have it listed in your Intellisense and understood by the compiler you will need to add a reference to the assembly that contains it. This assembly is called Microsoft.SqlServer.Types.

Recent URLs tagged Datatypes - Urlrecorder wrote Recent URLs tagged Datatypes - Urlrecorder
on 01-08-2009 6:46 AM

Pingback from  Recent URLs tagged Datatypes - Urlrecorder

ahmad wrote re: UdtTypeName and .NET data types in SQL
on 05-07-2010 11:28 PM

thank you very much .

Muzi wrote re: UdtTypeName and .NET data types in SQL
on 09-15-2010 11:36 AM

thanks man, this was very helpful

Bruce wrote re: UdtTypeName and .NET data types in SQL
on 11-06-2011 4:24 PM

ditto.  worked perfect for "geography" type... thx.

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)