Whidbey ADO.NET 2.0 User Defined Types (UDTs) and Visual Studio

  • Article

User Defined Types are a complicated feature, but it is hard to tell when you are using Visual Studio. All you need to do is create a Database project, connect to a Sql Server 2005 server, add a User Defined Type to your project and then click on Deploy. Magic, you are done. As is so common with complicated features made simple by wizards it is important to understand what is happening under the covers so that when something goes wrong (Murphys law kicks in quickly here!) you know what to do.

Important UDT deployment concepts:

  • User Defined Types can’t be dropped while in use.

  • User Defined Types can’t be modified.

  • User Defined Types need to pass verification on the Server before deployment.

User Defined Types can’t be dropped while in use.

This is HUGE, I can’t even remember how many times I have run into this problem. Starting a UDT project without being aware of this is bad mojo. What this means is that you can’t drop a UDT if you are using it in any table, function, stored procedure or trigger. You need to drop everything that may be using the UDT before you are allowed to drop it, I personally like to create a new database whenever I want to try out an unfinished UDT, that way I can drop the database and recreate it when I want to drop the UDT. (be aware that visual studio holds a connection to the server, if you use VS you need to close it before being able to drop database).

 

[EDIT: I have to eat my words on this one, User Defined Types can be modified to a certain extent with ALTER ASSEMBLY as Niels correctly points out in his Blog, Visual Studio does not use this feature so you will still run into the problem described if you rely on the “Deploy“ magic]

https://sqljunkies.com/WebLog/nielsb/archive/2004/07/28/3648.aspx#FeedBack

User Defined Types can’t be modified.

This ties in with the above point. Once you have created a UDT you can’t modify it, your only option at this time is to drop the type and register a new one. In VS if you change your UDT and deploy again it will automatically drop the current UDT and deploy the new one. If for any reason it is not able to drop the old UDT (if you have a table that uses it etc) you will get a deployment error.

User Defined Types need to pass verification on the Server before deployment.

UDT restrictions are checked when you attempt to load the assembly into the server, this means that even after your UDT compiles you may get exceptions from the server if your UDT fails any of the UDT requirements.

With this under our belts we can now take a look at what is happening under the covers when you click on Deploy. First the UDT will build, then we check whether there is a UDT of the same name on the server/database that your Database project is connected to. If this is the case we will attempt to DROP TYPE <mytype> and DROP ASSEMBLY <myassembly>. You must be a db_owner or db_ddladmin to be able to create or drop a UDT.

The next step is to CREATE ASSEMBLY <myassembly> FROM <myassembly.dll>. If I was doing this by hand <myassembly.dll> would be a physical location that is accessible to the Sql Server 2005, something like ‘\\mycomputer\myudtshare\myassembly.dll’. Visual Studio does something really cool here, instead of pointing Sql Server 2005 to a physical share it sends the assembly in binary form directly. At this point Sql Server verifies that your UDT meets all of the requirements, if it does not you will get an exception from the server. We have worked on making exceptions at least readable, let me know if you find any that just don't make any sense. Once the ASSEMBLY is created it calls CREATE TYPE <mytype> to complete deploying the UDT into the server and you are good to go.

Rambling out,

Standard disclaimer, this post is provided AS IS and confers no rights.