An Enigmatic Error while Returning UDT Results

Let's say we have a very simple (and silly) CLR UDT defined and loaded into SQL Server named CLRInt, which exists solely to wrap a .NET integer and provide a ToString() method. We can now try to perform a very basic manipulation of this type:

declare @x CLRIntset @x = '35'select @x

What would we expect this to print? "35" might be a reasonable choice, assuming that's what the ToString() method on CLRInt would produce. Unfortunately, if we're running a pre-CTP-15 version of SQL Server we instead see this beast of an error message:

An error occurred while executing batch. Error message is: Could not load file or assembly 'CLRInt, Version=1.0.1957.23696, Culture=neutral, PublicKeyToken=ec69b661def13a4e' or one of its dependencies. The system cannot find the file specified.

This is rather enigmatic. If we do anything but return the value then things work just fine: the assembly is clearly available in the server. This, however, is not a server-side error. Rather, it's coming from the client, which knows nothing of the assembly tucked away inside the server.

So, what do we do?

First, this will not throw an error on the CTP-15 release of SQL Server. Starting in that release, Management Studio will show the byte-representation of the data type. This may or may not be particularly useful, but it avoids the ugly error and it's much more clear what's going on.

Now, if we don't want to wait for CTP-15 we have a couple choices. The best is to say what we mean in our code---just manually get the string representation:

declare @x CLRIntset @x = '35'select @x.ToString()

Now instead of returning the UDT to the client we are returning a String, a data type which it can gracefully handle. The result: we print "35" as we'd expect.

Another choice on a pre-CTP-15 version is to install the assembly containing CLRInt into your GAC. Once it's in the GAC you can execute the first version of the code and Management Studio will load the object and call ToString() on it for you. Note that this may be a poor choice, since starting in CTP-15 Management Studio will not call ToString() for you---it will just show the bytes.

- Isaac K. Kunen, Microsoft SQL Server

This posting is provided "AS IS" with no warranties, and confers no rights.

Comments

• Anonymous
  May 17, 2005
  There is missing logic in your business goal. First, if you create a UDT that is an int from a .NET object, it will most likely take multiple times longer to use the UDT. Why? because instead of SQL Server reading a 4 byte value, it must wait for the object to be interpreted and then get handed an object that is bigger than 4 bytes. Clearly you are not thinking about using this object in a production setting where performance matters are you?
  I recommend you abandon creating UDTs in .NET since they will degrade the performance of any database that employs them.
  
  z.
• Anonymous
  May 17, 2005
  This is indeed---as mentioned---a silly example, but one which is useful in illustrating the exposition. You are absolutely correct that it wouldn't be a good object to use in production. Use an int: that's what it's there for.
• Anonymous
  July 27, 2005
  What I don't understand then is the purpose of the static ToString() function which must be implemented.
  
  If we try the sample using isqlw we get the binary data and not the output of ToString