After reading Niels’ comment about the framework class library list, a quick check proved he was right, System.Runtime.Remoting.dll was not on the approved list. Adding a reference to this library (using his cool Object Browser trick) and writing a one-line stored procedure:
[SqlProcedure]
public static void SomeTestProc()
{
TcpChannel t = new TcpChannel();
}
proved this. The error message from autodeploy is:
Error: Assembly ‘system.runtime.remoting, version=2.0.3600.0, culture=neutral, publickeytoken=b77a5c561934e089.’ was not found in the SQL catalog.
Using this method (note that you have to *use* a type in the assembly, not just have a reference to the assembly, for this error to appear), I went through the list of libraries that are allowed and some useful ones that seemed to be disallowed. My first intuition was close, this IS the list of allowed assemblies, it’s just a little out of date. By trial-and-error:
Out (but in list):
System.Runtime.Remoting.dll
System.Runtime.Serialization.Formatters.Soap.dll
In (but not in the list):
System.OracleClient.dll
System.Transactions.dll
Out (never on the list, just tried them):
System.Web.dll (Cache class)
System.EnterpriseServices.dll (DTC and others)
System.Messaging.dll (MSMQ)
System.DirectoryServices.dll (Active Directory)
To get “on the list” BTW, requires that an assembly go through a special review to ensure reliability. This would also require annotating the assembly with HostProtectionAttribute instances where needed. Note that HPAs affect individual methods at runtime, this is CREATE ASSEMBLY-time message. It has been postulated that it’s the presence of HostProtectionAttribute that indicates “OK to load”, but System.SqlXml.dll and System.Transaction.dll don’t have a single instance of an HPA (or any other distinguishing attributes I could find with Reflector) and they don’t produce the “not found” message.
Perhaps the review process is still a work in progress. After all, SQL Server 2005 is still beta. Guess that’s why I tell folks the list is subject to change during the beta process. There’s still work going on to further ensure SQLCLR is secure/reliable/scalable. Cool.