Came across something recently that I thought was odd (or a bug) when dealing with UDTs in SQL Server Management Studio.
If I have a UDT, say ComplexNumber, I deploy it to SQL Server 2005. Use it as a column in a UDT table, complextab. Insert a row:
INSERT complextab VALUES('1:1i')
Then attempt to SELECT all of the values:
SELECT * FROM complextab
I get the following error message:
An error occurred while executing batch. Error message is: File or assembly name 'ComplexNumber, Version=220.127.116.11, Culture=neutral, PublicKeyToken=17177e16a4b86577', or one of its dependencies, was not found.
Scratched my head and thought about it for a few minutes. Reason for this is that SQL Server Management Studio (SSMS) is just another client of SQL Server that uses ADO.NET. In order to use user-defined types with ADO.NET, you must deploy the assembly to the client. So putting the ComplexNumber assembly in the GAC or in the directory that contains SSMS fixes this "problem". It's not a problem, it’s by design. The SSMS will call ToString() ON THE CLIENT SIDE and display the value just fine.
1. If you change it to: "SELECT complexcol.ToString() FROM complextab" it works because ToString is being called on the server, not on the client.
2. If you run the same command from SQLCMD it display the binary value of the ComplexNumber column. That's because it uses OLE DB to talk to SQL Server, not ADO.NET. Interestingly, this is what SQL Workbench in Beta 1 did also. Guess the folks at SSMS decided to nicely format it for Beta 2.