I usually show folks the SQLCLR streaming table-valued functions and using SqlDataRecord with SqlPipe.SendResultsStart/SendResultsRow/SendResultsEnd on the same day. This sometimes brings up the question "which is better"? Since you can synthesize rowsets with both mechanisms and they both stream rows back to the client one buffer-worth at a time, its an interesting choice. However, with SqlPipe/SqlDataRecord, you can synthesize multiple rowsets in the same stored procedure. Just cruft up two different SqlDataRecord With multiple SendStart/SendRow/SendEnd blocks. Can't do that with any kind of table-valued function. You must "finish" one rowset (SendResultsEnd) before "starting" the next (SendResultsStart), though, you can't have interleaved output any more than you would with an ordinary cursorless rowset. Example follows:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void MultiRowset()
{
SqlMetaData[] m = new SqlMetaData[1] {new SqlMetaData("colname", SqlDbType.NVarChar, 5) };
SqlDataRecord rec = new SqlDataRecord(m);
rec.SetSqlString(0, "Hello");

SqlMetaData[] m2 = new SqlMetaData[2] { new SqlMetaData("another", SqlDbType.NVarChar, 5), new SqlMetaData("number", SqlDbType.Int) };
SqlDataRecord rec2 = new SqlDataRecord(m2);
rec2.SetSqlString(0, "Goodbye");
rec2.SetSqlInt32(1, 42);

SqlPipe p = SqlContext.Pipe;
p.SendResultsStart(rec);        // first rowset
for (int i=0;i<10;i++)
 p.SendResultsRow(rec);
p.SendResultsEnd();

p.SendResultsStart(rec2);       // next rowset
for (int i = 0; i < 10; i++)
    p.SendResultsRow(rec2);
p.SendResultsEnd();
}

People often ask about the set of base class library assemblies that can be safely used in SQLCLR. It usually takes the form "can I use assembly System.XYZ.dll in SQLCLR procedural code" or "why do I get "assembly System.XYZ.dll is not found" when I try and catalog my own assembly that calls this one? The ones is hear mentioned most frequently is System.DirectoryServices.dll (Active Directory support) or System.Management.dll (WMI support) or System.Remoting.dll et al. The only way you use these is to run CREATE ASSEMBLY on them yourself, which involves using PERMISSION_SET = UNSAFE. And cataloging all the dependencies. Not for the faint of heart.

I explain that there is a hardcoded list of assemblies that SQL Server will load from the file system and these assemblies have undergone an extensive code review to ensure they don't destablize SQL Server when they are running in process. Since some assemblies contain both crucial and "unsafe" (to SQL Server) classes (mscorlib.dll is an example that comes to mind) these libraries must also be decorated with instances of HostProtectionAttribute. This attribute gives the host (SQL Server 2005, in this case) the final say over whether to run a method (e.g. Thread.Start in mscorlib.dll). SQL Server is very picky about when it will run an HPA decorated method, its only if the calling assembly is CREATEd with PERMISSION_SET = UNSAFE. There's more about this in chapter 2 of A Developer's Guide to SQL Server 2005.

I refer to this list of assemblies as "the approved list". You can see most of the "approved list" assemblies by creating a Visual Studio 2005 Database/SQL Server project and choosing "Add Reference". This is the entire list of approved assemblies with one exception. Right before RTM "System.Configuration" was added to the list. I'm not entirely sure why, because although you can successfully use a .NET config file with SQL Server 2005 currently, it neither supported or encouraged (see my blog articles about this).

Why the long story? Because, in SQL Server 2005 SP1 another assembly made the list: System.Deployment.dll. I'm rather puzzled by this one too, because it has to do almost entirely with click-once deployment. Not sure why someone would want a sproc to do this, unless it was used to tie SQL Server to .NET project deployment in conjunction with Visual Studio Team System. But, its in there. Hmmm...

The libraries that I'd hoped would make the list eventually, perhaps in a SQL Server service pack when Windows Vista and WinFX are released, are the Windows Communication Foundation (System.ServiceModel.dll) and Windows Workflow Foundation (System.Workflow.Runtime.dll et al). I've heard a lot of folks ask the same question, but so far there's been silence on this. Since System.WebServices (but not WSE) is aleady "in there", there might be some uses going forward. As a look toward "intent", I ran ILDASM against the just-released May CTP version of these. Not a HostProtectionAttribute in sight. Oh well.

SQL Server 2005 doesn't support assembly versioning in SQLCLR. Recently in a talk I restated this as "SQL Server 2005 will not allow two versions of the same assembly to be cataloged into the same database using CREATE ASSEMBLY". Someone promptly pointed out that they could indeed run CREATE ASSEMBLY on two instances of the same code, if one was strong-named and the other was not strong-named. That is the case, but that is not support of assembly versioning. The .NET Framework Developer's Guide states "Versioning is only done on assemblies with strong names".

As an example, if I run CREATE ASSEMBLY on an assembly without a strong name, the query "SELECT name, clr_name FROM sys.assemblies" produces the following output:

notsigned_test versioningtest, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil

Note the version in clr_name is "0.0.0.0" regardless of the version number that is stored in the [assembly:AssemblyVersion] attribute in the file AssemblyInfo.cs. Attempting to catalog another version of the unsigned assembly, code or AssemblyVersion attribute change or not, produces the error:

Msg 10326, Level 16, State 2, Line 1
Two versions of assembly 'versioningtest, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil' cannot coexist in database 'Northwind'. Keep one version and drop the other.

If I have strong named assemblies, I can only have one strong named version that differs by version. Running create assembly (on a strong-named "version 1") gives the following output using "SELECT name, clr_name FROM sys.assemblies"

notsigned_test versioningtest, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil
v1                  versioningtest, version=1.0.0.0, culture=neutral, publickeytoken=3e0cd30f6da0843f, processorarchitecture=msil   

So I DO have "two copies of the same" assembly in the database at a time. However attempting to run CREATE ASSEMBLY on a strongly typed version 2.0.0.0 yields the following error.

Msg 10326, Level 16, State 2, Line 1
Two versions of assembly 'versioningtest, version=2.0.0.0, culture=neutral, publickeytoken=3e0cd30f6da0843f, processorarchitecture=msil' cannot coexist in database 'Northwind'. Keep one version and drop the other.

So assembly versioning is not supported. Note that you can have assemblies with different 4-part names that differ only by culture. Assemblies that include a culture other than "neutral"; these are resource-only assemblies such as localized strings for different cultures. How does SQL Server 2005 decide which culture to use? SQL Server itself really doesn't decide, but that's another story for another day.

Been on the road lots lately, not much blogging, I'm afraid. I'll be making up for that...

I "found" a new command line utility today in SQL Server 2005. Maybe everyone but me already knew it was there. Reading up on replication, I came across mention of the "tablediff" tool. You feed it and source and destination table and it will spit out a report of data differences (the table definitions must be identical), on a row level or column level. It's meant to allow you to fix problems if you perform an operation that gets databases involved in transactional replication get out of sync. But it can be used outside of replication just as well. It lives in the ...Microsoft SQL Server\90\com directory.

Nice diagnostic and synchronization tool. My favorite option is -f which generates TSQL statements to get the tables back in sync.