Fun with displaying UDTs in SSMS

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=, 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.

Note that:
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.

3 thoughts on “Fun with displaying UDTs in SSMS

  1. This is unfortunatelly the expected behavior (not a bug). I really dislike this behavior and I am looking for customer feedback on what could be done to improve it. Would you expect to see binary? XML? ToString? Different default behavior when the UDT is present and when it is not?

    Any thoughts or suggestions appreciated.

  2. Hi Angel,

    Displaying binary was OK and technically correct, just looked ugly.

    As irritating as this behavior is on occasion, I think it is reasonable, too. UDT don’t have implicit conversion TO varchar (they do FROM varchar), and you certainly wouldn’t do implicit conversion to varchar on a "SELECT * FROM…". But… You DO want to see readable strings in SSMS, as you do for datetime. You don’t necessarily want the SERVER to call ToString by default when you do a SELECT. For UDTs that actually are binary, that would be wasteful. Would also stop me from instantiating new UDT instance in a client and calling its IBinarySerialize.Read to fill it up.

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.