An Enigmatic Error while Returning UDT Results

  • Article

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.