Remoting Over TDS

This week saw the release of a long awaited white paper: Using CLR Integration in SQL Server 2005. This white paper is the first step in what I am sure will be an ongoing stream of best practice information for using the common language runtime inside the database engine.

One area in need of some additional guidance will be the design and usage of user defined types (UDTs). In SQL 2005, if you need a columnar data type outside of the normal varchar, int, and datetime etc. types, you can write your own. As the paper outlines, UDTs are not a way to achieve object-relational extensibility, so we won’t want to create a Customer UDT. Instead, UDTs will augment the basic scalar data types in SQL Server. The engine can index a column of UDTs and enforce RI constraints against them.

All of the examples I’ve seen to date deal with UDTs from TSQL and CLR code executing inside database. Wally McClure found the following error message when using a SELECT statement from the query tool:

Could not load file or assembly "Assembly Name, Version=xxxx.xxxx.xxxx.xxxx, Cultere=neutral, PublicKeyToken=null' or one of its dependencies. HRESULT: 0x80070002 The system cannot find the file specified

Niels Berglund explains why this happens. If you use a ToString on the column in the SELECT statement you’ll get back strings, but with no methods invoked you fetch actual instances of the UDT type. The query tool’s appdomain has to load the assembly for this type. Unless the assembly is properly deployed the load will fail.

The interesting thought here is that there is a new .NET remoting channel in town: the tabular data stream protocol used natively by SQL Server. Along with this capability comes all of the intricacies of sharing type across a boundary, like versioning and GAC deployments. Whoa.