Niels SQL Server Blog

The following is something that has come up on a couple of the SQL Server 2005 beta news groups.

So, you have created a UDT and deployed it into SQL Server 2005. You have created a table with a column of the UDT type, and inserted some data. You're now testing it from SQL Server Management Studio (SSMS), and the chances are pretty big that you're running a query something like this:

select udt_col_name from table_name

or you may do something like this:

DECLARE @p udt_type
SELECT @p

Notice that you are doing a select on the type itself, not ToString() or anything like that.

Anyway, you may be surprised when you get an error in both examples: "An error occurred while executing batch. Error message is: File or assembly name 'Your_Assembly_Name, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null', or one of its dependencies, was not found."

The error happens because SSMS acts like any other CLR client (SSMS uses ADO.NET under the covers); it retrieves the UDT and then on client-side (under the covers) tries to call ToString(). Before it calls ToString() it tries to load the assembly. So for this to work, the assembly needs to be located either locally together with the application, which in this case is SSMS or in the GAC. Bob wrote about this in his blog post.

So now you give the assembly a strong name, you install it in the GAC:

gacutil -i path_to_assembly

and you re-deploy the assembly. Now, when you re-run the query everything works fine!

However, what would have happened if you had initially given the assembly a strong name and then, after having received the error mentioned above, installed it into the GAC? Or alternatively, just copied the assembly to the path of SSMS? Note, without closing SSMS.

Well done to the ones of you that said you'll still get the error mentioned above. The follow up question is obvious: why?

Answer in the comments section or email.