The last blog entry brings up the question of what I mean when I say something is "unsupported" in SQLCLR. Becuase I've said that J# is "unsupported". This doesn't mean that it won't ever work or that you couldn't actually get technical support for it, given enough time, energy, and money.

Technically, its possible to run almost *anything* in SQL Server if you catalog it as UNSAFE. But if your library doesn't follow SQL Server's rules for reliability and does something that could compromise the stability of the server, its appdomain could be unloaded as a last resort. Oh. Only a subset of the BCL are supported; to see this subset, create a Visual Studio Database/SQL Server project, and choose "Add Reference". Note that only a subset of the base class libraries appear. These are the ones that have been hardened according to the SQL Server reliability guidelines. Note that this contains the support libraries for VisualBasic.NET and Managed C++ (C# uses no language-specific support libraries), but not for J#. Because of the COM interop, they'd have to almost completely rewrite it to be compliant. That's what I mean by unsupported, I don't mean that it technically isn't accomplishable. Note also that there's no J# Database/SQL Server project in Visual Studio. That's a clue. And although *managed* C++ is a supported language, you have to compile with a special /safe switch, which enforces reliability limitations.

On the other hand, it's always been my contention that the most unsafe CLR code is safer than an extended stored procedure. Extended stored procedures are analogous to tweaking with the kernel of an operating system; there has to be extended test/maintanance plan because, unless you're the SQL Server team, you don't "own" the code you're running under. There's now notes in the BOL, under Programming Extended Stored Procedures, that read: "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work" and "Use CLR Integration instead.".

Looking at the Feb CTPNotes file again, there's another change that caught my eye. DROP ASSEMBLY has changed with respect to dependent assemblies. In past, you had to drop assemblies one at a time, so if assembly A called assembly B, you first dropped A then B. Now dropping A drops B automatically if B has the "is_visible" flag is false for B.

Although the looks like a convenience change at first, it actually solves a problem people run into when they go outside the mainstream of the supported BCL assemblies. If, for example, you used a library of your own that had a reference to System.Drawing (it's a library that draws an icon, but you don't intend to use this portion in SQL Server), this triggered a set of BCL references, some of them circular references. The only way to catalog something like this to SQL Server entailed cataloging as UNSAFE and (because of dependencies) also cataloging many unsupported base class libraries. But you couldn't drop it all because of the circular references.

Another example of this is using the J# language. Because J# support libraries use COM interop, the most trivial J# program (I just added two numbers together) must be cataloged as UNSAFE and results in 4 J# support libraries and 6 or so unsupported base class libraries being cataloged as dependencies. It's now possible to drop the J# assembly and have it drop all the associated assemblies at the same time.

The data access team (known as DataWorks) has started up a team blog. Some of the individual team members, like Angel Saenz-Badillos and Sushil Chordia, have been blogging for a while, this one has posts from all members.

One of the first posts was information about the upcoming SqlClient and SqlServer provider unification by Pablo Castro. The unified provider isn't in Feb CTP, but will be in an upcoming release soon. Pablo mentions that SqlConnection and other classes that can be shared between providers will be, but that SqlContext will still be retained for in-database- specific classes. By my calculations, that leaves (as in-database-specific):

SqlPipe - encapsulates a data stream back to the client
SqlTriggerContext - provides information in a SQLCLR trigger
WindowsIdentity - used for impersonation when accessing external resources (e.g. files) where you need a Windows identity

The only thing that I'll miss is the SqlDefinition/SqlExecutionContext classes. I'd been told those won't be in for this time; hopefully they're in the next major release.

They'll also be an upcoming in-depth article when the unified provider ships.

Thanks, Pablo! And Alyssa!

In this last entry on Service Broker enhancements I inadvertantly referred to the new poison message handling as poison conversation handling. Well, maybe it wasn't so inadvertant. So what's the difference between Service Broker's poison message handling and traditional poison message handling?

A poison message is a fact of life in transactional messaging. When a message is received from a queue, often some database action occurs as part of the same transaction. If the database action fails (say, insert of a row based on a field in the message that happens to be a duplicate key) the message is put back on the queue. Where it is received again... If the database condition that caused the first rollback to happen hasn't been resolved, the transaction will roll back again..and again..hence the term posion message.

Usually poison message handling shunts the message off to a dead letter queue. Where it can be safely ignored while the application goes on. Oh. The problem with this is: suppose the message you are ignoring is a million-dollar order. Or the executive's December check. The database transaction may have rolled back because overflow occurred on an internal variable (especially with extremely large dollar figures). I've personally seen the “executive December check overflows payroll counters“ one, back in the days of COBOL. They used fixed point decimal just like SQL/RDBMSs do today.

Since the primitive concept of Service Broker is the conversation, not the message, the message should not be ignored.
You could lose the million dollar order. Or produce cranky executives. The programmer who designed such an app (and didn't watch the dead letter queue) could be fired. There's something wrong with the conversation, it should be shut down.

The new "posion message handling" actually goes further than that. After 5 receives of the same message, Service Broker shuts down *the queues on both sides of the conversation*. You can recover from this by:
1. Either end the conversation or recieve the message without a rollback
2. And reenable the queues

You can still implement your own poison message handling, using any of the suggestions we described in our "First Look" book. You have 4 retries to do something on your own, before the automatic poison behavior kicks in.

I've been doing some experimenting with the new SQL Server Service Broker features in Feb CTP. You can read about them in the CTPNotes.doc file; I won't repeat the information here. The features are:

1. Improved Endpoint Security - authentication option NONE is not longer supported
2. DEFAULT message type and contract
3. Poison conversation handling

DEFAULT message type and contract came about due to feedback that the DDL to create a simple broker applications consisted of too many pieces. You needed to define MESSAGE TYPEs, CONTRACT, QUEUE, and SERVICE to define the simplest application. The first time this behavior change was described to me (it was some of my students among those who complained about the complexity after all), I thought they were going to loosen things up a bit to work without a contract. But broker uses contracts to enforce conversation integrity. In order to receive a message, a service has to be defined with a contract that's enforced when messages are being put on the queue. No contract, no user messages can be received. Hmmm...how would they do it?

You can now define a broker SERVICE by only defining QUEUE and SERVICE objects. However, the SERVICE must be defined to use a new built-in contract named [DEFAULT]. This contract specifies that a built-in MESSAGE TYPE, also called [DEFAULT], can be sent by either side (by ANY). When you issue a BEGIN CONVERSATION DIALOG without a contract, it uses the [DEFAULT] contract, not NO contract. When you SEND a message without an explicit MESSAGE TYPE it sends the [DEFAULT] message type.

So you're NOT using contract-less and message type-less conversations, you're using a specific contract and message type called [DEFAULT]. You just don't have to define them yourself.

There's a code example is the Feb CTPNotes.doc file (which is why you should always “read the readme file”), try it out for yourself and see.

When reading the CTPNotes file from the new Feb CTP build I stumbled across the fact that the XML schema that contains SQL data types (http://schemas.microsoft.com/sqlserver/2004/sqltypes) is now built-in to the server. Although this may not mean much to most people, it gave me the chance to try something that Dan Sullivan thought up for the first rev of our SQL Server 2005 class. It works now.

One of the enhancements to SELECT...FOR XML is ability to request that the XML it produces be prepended by an XML schema that describes it. A recent change allows you to choose the namespace for that schema. Dan's idea was to add the prepended schema to create a schema collection. After storing the FOR XML outside in an XML schema-valid column, you could make updates to the column that would be validated by the schema. You'd set this up like this:

declare @x xml
select @x = (select * from authors for xml auto, type, xmlschema('urn:authors')).query('*[1]')
create xml schema collection authorsxsd
as @x
go

create table authorsxml (
 id int primary key identity, -- primary key required if XML index needed
 authors xml(authorsxsd))
go

declare @x xml(authorsxsd)
set @x = (select * from authors for xml auto, type, xmlschema('urn:authors')).query('/*[position()>1]')
insert authorsxml values(@x)

The XML Schema produced in the first step will now validate any information entered or updated in the table.

Why the Feb CTP change makes this work is FOR XML....XMLSCHEMA uses the SQL data types schema that's now built in. In previous betas, you could use this schema (error: not built in) or add the schema manually (error: it is built in [but it wasn't]). Thanks SQL Server 2005 XML folks, for this.

In the new Feb CTP release, how your implement a table-valued function in SQLCLR has been re-architected. This is in the readme (CTPNotes) This was done because implementing ISqlReader was quite complicated and overkill for most scenarios. Chapter 3 of our book "A First Look at SQL Server 2005 for developers" contains a very simple TVF (Bernoulli) implemented using ISqlReader. It contains over 400 lines of code. Many of the methods are stubbed-out because they are never used, but must exist to satify the interface definition. Using the new implementation this method would be less than 15 lines of code.

The new TVF implementation requires three pieces:
1. The SqlFunction attribute with the new field FillRowMethodName.
2. This attribute is applied to a method that returns either IEnumerable or IEnumerator.
3. FillRowMethodName points to a DIFFERENT method (in the same class) that has a special signature.

The methods in steps #2 and #3 have to be public static. Many of the collection classes in the BCL (e.g System.Array) implement IEnumerable or IEnumeration already, or you can write your own implementation.

The FillRowMethodName method has the following signature:

public static void FillIt(Object o, out int col1 , out int col2...)
   where the first arg is object returned by method in step #2
   where the varargs arguments (col1, col2....) are the columns that will be returned.

MoveNext is called on the underlying IEnumerator (in each case) until it returns false. Each time MoveNext returns a value, the FillRowMethod is called. This generates the rows. The number of columns is determined by the exact signature of the FillRowMethod. In this example, a 2-column table is returned.

Interestingly, the 2-nth arguments in your FillRowMethohd must be declared as "out" variables in C#. In my cursory testing, if they are declared as "ref", the method failed with the error: "argument n cannot be NULL" when the TVF implementation calls your FillRowMethod. This is interesting for VB.NET programmers because there is no direct variable qualifier keyword that corresponds to C#'s out. Or is there?

When .NET was first released a friend of mine, Jose Mojica, published "The C# & VB.NET Conversion Pocket Reference". And it names the following VB.NET equivalent for "out":

Imports System.Runtime.InteropServices
' signature of a FillRowMethod
Shared Sub FillIt(o as Object, <Out()> ByRef col1 as Integer, <Out()> ByRef col2 as Integer...)

Works great, Jose, my VB.NET TVF is working fine. If you're doing cross language work in .NET, I highly recommend this book.

Hi all. Not much blogging out of me lately. I've been on vacation and, in between, I've been teaching SQL Server 2005 at Microsoft Sydney to some of Australia and New Zealand's finest, including Russell Darroch, Greg Low, Chris Hewitt and Brent Challis. During the class Greg worked up his all-encompasing trigger to prevent cataloging objects in the master database. Probably partially inspired by seeing my do this a few times by mistake in demos. Some other folks in the class worked on *their* application specific features inspired by the encryption built-ins (e.g. encryptbykey), XQuery functionality, FOR XML PATH, and Service Broker. Thanks folks, I had a great time.

More people answer the question "what's the feature you most want to hear about?" asking about Service Broker each class. It's amazing to see Broker's "recognition curve" increase steadily since I started teaching SQL Server 2005 in...uh...August 2003. And to watch momentum building for this release in general.

By now, I'm sure you've heard that there's a new CTP (Commnunity Technology Preview) released this week. I've got the CTPNotes file. Be SURE to read this one carefully. There's a lot of new stuff in this build. I'll be home mid-week to start on it in earnest.

Right now I'm just back from watching the sun go down at Manly Beach. Morning was spent navigating the waves and getting myself sunburnt, then it started sprinkling rain around 3. Cleared up right after dinner. It's going to be hard to leave summer.. oh that's right.. its autumn here already.

More technical content shortly. Got some blog comment responses to catch up on too...later.