After having been immersed in .NET since the alpha version, I'd lost touch with how many products use OLE DB and ODBC. After all, when you talk about SQL Server and SQL 92/99/2003 standards, the standard API for a SQL database *is* still SQL CLI (call-language interface). And the canonical implementation of SQL-CLI is ODBC. When I spoke recently about SQL Server 2005 Web Services as a way to acheive interop between unlike client architectures for SQL Server, someone reminded me that ODBC drivers for "unlike architectures" have existed for years. Uh, yes, of...course.

Parts of SQL Server 2005 like linked servers, replication, reporting services, and DTS (renamed SQL Server Integration Services (SSIS) last week) use OLE DB and ODBC as well. Although SSIS supports ADO.NET too.

SQL Server 2005 ships with a new communication layer known as SNI and a new OLE DB provider and new ODBC driver that use it. These are bundled together in a part of the product that runs on both server and client called SQL Native Client (or SNAC for short). Folks that have existing non-.NET applications are *really* interested in:

1. How the SNAC provider/driver supports the new data types
2. Any subtle differences between the new provider/driver and the current ones

Two weeks ago, someone asked about support of the new "MAX" data types VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) in both SQLOLEDB/SQL Server ODBC and SNAC OLE DB/ODBC. For example, does VARCHAR(MAX) resemble VARCHAR or TEXT? So I took out my handy-dandy OLE DB Rowset Viewer and had a look. I looked at column metadata (DBSCHEMA_COLUMNS) and the  DBCOLUMNINFO structure (after a SELECT) of the following table:

CREATE TABLE testclob (
  avar VARCHAR(8000),
  amax VARCHAR(MAX),
  atext TEXT)

Answer is: VARCHAR(MAX) looks almost exactly like a TEXT data type (as far as the API is concerned of course; they're way different as far as TSQL is concerned), both in SQLOLEDB and the SNAC OLE DB provider. And both providers yield almost the same metadata. The big caveat there is the "almost". Here's the two almosts:

1. Both VARCHAR(MAX) and TEXT have the same capabilities/properties with respect ISLONG and MAYDEFER (ie, supports deferred fetch). But, in the Schema Rowset the character_max_length and octet_length is 2147483647 (2gb) for TEXT and 0 (that zero) for VARCHAR(MAX). Be careful using this to size to allocate buffers.

2. SNAC's DBCOLUMNINFO listed (maximum) ColumnSize as 4294967295 (4 gb?) rather than 2 gb as SQLOLEDB provider did.

I'd thought that SNAC's behavior was going to be identical to SQLOLEDB to ease migration, and it appears as though, except for the one metadata anomaly this is true. And I didn't really expect SNAC and SQLOLEDB to be exactly the same regarding *new* features; after all, that's the point of using the SNAC provider/driver, new feature support. There may be a few other subtle differences, more on this later.

I heard that when SQL Server 2005 ships, SQLCLR (the ability to execute .NET code in SQL Server) will be disabled by default. This is part of an "off-by-default" philosophy that goes "because there are so many revolutionary new features in SQL Server 2005, and DBAs and programmers may not have had time to digest the ramifications of using all of them, you'll have to enable them, one at a time, when you understand them. They'll be off until you make a conscious decision to turn them on". IIS 6.0 works this way with its programming extensions; for example, you must enable using ASP.NET or ASP classic.

Thought I'd better look up how to turn it on and off:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
-- turn it on
EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

-- or turn it off
EXEC sp_configure 'clr enabled', 0
GO
RECONFIGURE
GO

That was easy. I turned it off and restarted SQL Server wanting to see what error message it would produce. Some folks I've spoken to claim to want it off even though they understand it, because SQLCLR loads the .NET runtime. This takes about 10 meg of memory, from SQL Server's "normal" memory pool (it does not use the MEM-to-leave pool as in-SQL Server COM components do). Turning SQLCLR off did not cause the CLR to be unloaded (or at least it didn't produce a log message to that effect). So I wanted to see if I could get the .NET runtime to load if SQLCLR is disabled. Started with:

CREATE ASSEMBLY foo FROM 'c:\foo.dll'
GO

Even though foo.dll doesn't exist on my machine, this statement normally causes to .NET runtime to load. Even though it errors out with the message:

Msg 6501, Level 16, State 7, Line 1
CREATE ASSEMBLY failed because it could not open the physical file 'c:\foo.dll': 2(The system cannot find the file specified.).

The reason that the .NET runtime is loaded in this case in that SQLCLR internally calls Assembly.Load("c:\foo.dll") (or some variation) to load the assembly and validate it using the reflection APIs. Both Assembly.Load and reflection are, of course, managed code, requiring the runtime.I expected a different error this time, because SQLCLR is disabled. Same error. And the log revealed that the .NET runtime had been loaded. Even though SQLCLR is disbaled. Hmm....didn't expect that. Just to see how far I could go, I got out a real assembly and ran:

CREATE ASSEMBLY MetricConverter
 FROM 'c:\types\metricconverter.dll'

This succeeded, I'd cataloged my assembly. Hmm... How about:

CREATE FUNCTION convertme(@a FLOAT)
RETURNS FLOAT
AS EXTERNAL NAME MetricConverter.[DM.EssentialYukon.MetricConverter].KilometersToMiles

This succeeded too. At this point I started to doubt that I'd actually turned SQLCLR off. Then I ran:

DECLARE @f FLOAT
SET @f = dbo.convertme(42)
PRINT @f

This failed as expected:

Msg 6263, Level 16, State 1, Line 2
Execution of user code in the .NET Framework is disabled.

So the 'clr enabled' option disables *running* user CLR code in SQL Server 2005. It doesn't diable loading the runtime. Or cataloging database objects, like assemblies and UDFs, that use SQLCLR. It's a convenience to allow DBAs (usually the only ones with this permission) to catalog these objects before allowing the actual user CLR code to be executed. If you're really concerned about the 10 meg, don't use the DDL. Note to self: test *everything* before making assumptions. I guess that goes along with “off-by-default”.

Just finished watching Ichiro break the single season hit record on TV. I'm a Seattle Mariner fan from way back when, and a big Ichiro fan. Most likely some of the students from my class in Tokyo this summer were watching it too. The announcer said it was on lots of channels there. This summer I realized they got a lot of live/taped Mariner games on TV in Tokyo along with many Japanese baseball league games by watching TV in my hotel. Congratulations to Ichiro! But it reminded me of my world's worst marketing idea.

It was 2002, I think, middle of the post-year-2000 high tech bust and we were trying to find ways to increase class attendence. At the time, we had many "high-profile" instructors (MSDN called them "rock star speakers" this year).

I'd just seen Ichiro bobble-head doll night, where folks lined up around the block to get into the ballgame because they were giving away this collectable. The Ichiro bobble-head doll given away for free that night supposedly sold for hundreds of dollars on EBay the very next day. They should even be worth more now.

My idea was to give away bobble-heads of all the DM instructors with a very limited number of the "high-profile" ones. One doll/student at each class. After watching folks line up for collectables, I figured that this actually might help sell classes. Sort of like the attendees you see wandering around PDC with armloads of SWAG. I once worked with someone that joined IEEE at a conference just to get the free coffee cup. Since there would be less copies of the "high-profile" ones, they'd have to come back again and again. I was so enamored with the idea that I even found a place on the internet where they could make them (personal collectables) up.

My cohorts didn't think so highly of the idea when I posted it to our internal mailing list. A few of them couldn't stop laughing over the outragousness (or ridiculelessness) of it, most just ignored me. But my favorite comment was from Craig Andera, an amazingly bright guy and very witty; for some reason not that high-profile. He wrote to me "I can just see my students unwrapping their collectable and saying "damn, I got another Andera, I got 5 of these. Anyone want to trade?" Cracked me up.

Beginning and end of my career as a marketer. And why programmers (at least most of us) shouldn't be marketers.

Last week I promised some of my students that I had an article in the works on the intricacies of SQL Server 2005/.NET 2.0 Query Notifications, both from a server implementation and client consumer point of view. Yesterday I found out that the article “shipped” to (was officially posted on) the website. You can find it here. This article is part 4 of a 6-part series on ADO.NET 2.0/SQL Server 2005 I'm writing for MSDN Data Access and Storage Developer Center. The other articles might be useful to ya' too...

It turns out that you can force SQL Server to use FX libraries that are not on the approved list. Deploying user code that uses these through Visual Studio produces:

Error: Assembly 'system.runtime.remoting, version=2.0.3600.0, culture=neutral, publickeytoken=b77a5c561934e089.' was not found in the SQL catalog.

But that's because Visual Studio uses CREATE ASSEMBLY "from bytes", passing in the byte array. You can also use "CREATE ASSEMBLY foo FROM 'c:\types\foo.dll'. SQL Server then searches the directory specified (c:\types in this case) for dependent assemblies. When you use VS autodeploy it doesn't do this, making "automatic cataloging of dependent assemblies with IsVisable=No" difficult (impossible?) to prove when using autodeploy.

When I use CREATE ASSEMBLY with a file on my "simple remoting program", there is a more telling error:

Msg 6581, Level 16, State 1, Line 1
Could not find assembly 'system.runtime.remoting' in directory 'c:\types\'.

Oh. Copying 'System.Runtime.Remoting' to c:\types produces another "Could not find assembly". Eventually I put my assembly in the FX lib directory so it can resolve everything and set the permission_set to unsafe. This works and I've deployed it. Looking in sys.assemblies, it's taken 15 FX libraries that are not on "the list" with it. They're cataloged as "normal" libraries with IsVisible=True in the metadata. That means you can't declare catalog or use their methods from SQL Server directly, only from other user assemblies, subject (of course) to security.

It also DOESN'T imply that these libraries are unsafe in general, just when you use SQL Server as a runtime host. SQL Server likes to do things like control it's own threading, exception handling, memory allocation, etc, etc. Most/all other current runtime hosts don't. It's not going to USE most/any of these in my one-line program, mind you, it's just following dependency chains in the manifests. Things may work fine.

BTW, just in case you didn't gather this, this is NOT NOT NOT RECOMMENDED. Just meant to answer the question "can you...". I, for one, am GLAD there's a list. That they are definately looking out for reliability/scalability/etc. Very cool.

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.

My apologies to anyone who showed up at SQLPass this morning for the pre-pre-conference talks, which was supposed to include a day of SQL Server 2005 for developers given by me. If you were there, you probably noticed that I wasn't. A storm called Hurricane Jeanne saw to that. The Orlando airport was closed from 5pm Saturday to noon today. Thought I'd be arriving too early (Saturday night) but I wanted to make sure I'd make it. That wasn't soon enough. The talks may be scheduled for some other venue, but not at SQLPass. The rest of the conference is proceeding though, starting with Tuesday's preconference.

I've never had to cancel a conference or class before. The closest was interestingly, a VSLive in Orlando. I had a Sunday flight for a Monday talk. Portland to Denver to Orlando. Only someone left a door open at PDX, leading to the evacuation of the airport. Planes were all at least 2 hours late, but I cruised into Denver with confidence, knowing I had a “direct-with-one-stopover” flight. However, the Denver-to-Orlando portion was cancelled and I received a ticket for the next day. Sigh, I called and cancelled.

When I went back to arrange for tickets back to Portland and told the person at the counter my tale of woe, she said “there's another alternative but you probably don't want it”. The previous person had told me there was no alternative. Oh. The alternative was to fly from Denver to LAX and then to Orlando on a red-eye. Arriving 6:15am. I jumped at that one, called and un-cancelled, and arrived at 7am for a 10am talk. And I don't sleep on planes at all.

After that incident, I always arrive an extra day early, to “be sure“. Saturday, running through possibilities and remembering last time, I even checked the LAX-to-Orlando red-eye for Monday morning. It was cancelled too.

So I'm home in rainy Portland OR, in the rainy Pacific Northwest. Last SQLPass I attended was in rainy Seattle, where folks complained about the weather. It's going to be over 80 degrees today and sunny here all week. Oh well...

Back from vacation, drove from Ashland back to Portland Saturday, then Portland to Redmond Sunday. A lot of driving. The Michael Brundage XQuery book was even better the second reading. Joe Celko's book was excellent (what else?) also. So was OSF presentation of Henry VI, Much Ado About Nothing, and King Lear, but my favorite was a non-Shakespeare play entitled "Humble Boy". It was about "aha moments" in the life of a scientist.

Back to teaching and to SQL Server 2005 SQLCLR.

While teaching folks about SQLCLR, I've always said that SQL Server uses a hardcoded list of "approved" framework class libraries (libraries that are part of the .NET framework, e.g. System.dll) to determine which libraries are allowed to load. Also, although user assemblies are cataloged as SAFE, UNSAFE, or EXTERNAL_ACCESS, which FX (framework class) libraries are allowed to load does not vary with safety level. Which methods in those libraries can be called without causing a security exception does vary; however if a library is not "on the list" it won't be loaded even if referenced in UNSAFE user assemblies. Folks always want to know where the list is (ie, "prove it to me").

Lately I came across the list in a rather unique way. Create a Visual Studio B1 SQLCLR project in any language (a Database/SQL Server project). Now choose "Add Reference" either by right-clicking on references or any only means. Note the list of libraries you can choose. These include only the FX assemblies allowed to load and also all of the libraries at your safety level or below that are already cataloged in the database that autodeploy is pointing to. Also, when you change the safety level in your project, the FX assembly list doesn't change. Aha...

I'd also like to see a browse button on the AddReference dialog as well. Not having a browse button means you can't add a user library that's not already cataloged. Why I might want to do that is a story for another day.

After a summer of being a homebody, fall and winter will be *lots* of traveling. I'll be doing the day of SQL Server 2005 for Developers at SQLPass, as a pre-pre-conference talk (a day before the pre-conference) later this month. And Ascend 2, in at least three continents, so far.

Interestingly Ascend 2 starts almost exactly a year after Ascend 1. Both started about the same time as our fall trip to Oregon Shakespeare festival in Ashland. Last time around Dan was writing labs and wrote an ObjectSpaces lab (there, I've said the 'O' word) around object queries over a relational-ized version of the XML rendition of the collection of Shakespeare's plays. Query was pretty fast, considering how many plays he wrote. This year it will be XQuery and recursive CTEs, although these were around last year too.

I'm actually taking a little light reading on the trip, Michael Brundage's XQuery book (reading it again for detail) and also Joe Celko's new "Trees and Hierarchies in SQL for Smarties" book. I'm meeting up with Joe at WinDev, and I'll need to have read the book; hope there won't be a test. Listening to both sirens of the relational trees vs. XML siren song. I'm not sectarian about this.

OSF is non-sectarian as well, counting Bill Joy and Bill Gates among it's donors. And us, Mary and me.

Off to the land of planes, trains, and automobiles, perhaps I'll see you on the road...later.

Thinking ahead to the SQLPass conference and also back a few weeks to the Ascend Airlift. Almost forgot that at the airlift I met Angel Saenz-Badillos for the first time in person. He introduced himself at an Ask the Experts session; I was doing the asking. He's been writing about ADO.NET 2.0 and gave me a lead on a transaction investigation project he was doing. It's on my "long list" but hasn't made it to the short list yet. I was reminded because he's on my Blogroll to the right. Check out his blog.

Interesting how you "converse" with folks on newsgroups for years without actually meeting the person you're conversing with. I'd met up with Angel years ago on microsoft.public.dotnet.framework.adonet; we were both answering questions on all things ADO.NET, but seemed to be the only ones answering Oracle questions. He was supporting the Microsoft OracleClient provider, I was trying them all (ODP.NET, OracleClient, and DataDirect.NET Oracle) and answering folks' questions. Now we're both working on SQL Server 2005 and crossing paths again. This time around .NET is used in the server as well as on the client. It was good to be able to say hi for real.

Back to XML and schema collections in SQL Server 2005...

After my experiences with Office 2003 schemas yesterday (thanks for getting me going, Dan), last night I went out onto the Internet, prowling around at 1-2am looking for XML schema repositories. The most likely candidates are mostly gone or inactive. Microsoft's http://www.biztalk.org now links to http://www.microsoft.com/biztalk, information about the Biztalk product. No schema repository here, that I could see. James Tauber's http://www.schema.net has a "returns soon" sign on its digital door. The XML.ORG Registry seems to have the most, although less than I'd expected. There were actually some with namespace URI's for in 1999 XMLSchema namespace, and some DTDs too. Hmm...

Why all this interest and schemas and schema repositories? I'm looking for some non-trivial XML schemas (preferably interlocked sets of schemas, ie. with imports) along with exemplar documents, to try out with SQL Server 2005's CREATE XML SCHEMA COLLECTION and static-typed XQuery. If you know of any, send me links. Should make for some enjoyable times on planes in the near future, like puzzle books.

And what does this have to do with Peaches en Regalia? Once upon a time, in a "previous life", I wrote a program called SMUS2T that converted Amiga SMUS music format to a format for a popular sequencer program, Dr T's KCS. SMUS2T is so obscure that a Google search returns no hits. I then set out to find use cases in test data to break it, naturally. Settled on Peaches en Regalia (by Frank Zappa) because, if you've ever heard it, it contains almost every musical sequence that my spec had. Quintuplets, septuplets, perfect test data. And I liked listening to it, at least I did for the first 50 iterations or so. Two days later, the program could convert it and all 4 tracks played synchronized from beginning to end. And it could convert Yusef Lateef tunes correctly as well.

So now I'm looking for test data again. XML schema collections this time. To test something new. I'm sitting here listening to the digitally remastered version of Peaches from the Hot Rats album, sounds different after he re-produced it for the CD medium. Although I think my favorite track is now "The Gumbo Variations".

I just answered this question on the "public/private" sqlclr newsgroup, but now I'm wondering myself. What IS all that stuff doing in sys.assembly_files? If you've done autodeployment of a SQLCLR assembly lately, take a gander as the metadata view sys.assembly_files. There's not only the binary assembly code and the debug symbols (pdb file) but also source code. And finally, Visual Studio project file, .csproj or .vbproj.

You actually need:
  The real assembly code to run the assembly. Of course.
  The debug symbols if you want to enable in-process debugging

You might like the source code because then its stored in the database like T-SQL code is. There was a thought at one point that you'd deploy your source code and SQL Server would run a compile on it inside SQL Server. That's not gonna happen (at least this time around), and I sort of wondered why people wanted it, maybe because T-SQL stored procedures work that way, but more likely for manageability. That way the actual code is backed up with the database. Anyhow, in beta 2 the source lives in the server, but is not used by the execution engine. Interestingly, if you choose to use an external key file to codesign the assembly, maybe to deploy it to clients for deployment to their GAC, this isn't stored in the database. At least I haven't found it so far.

Still not sure why the VS project file is there though, maybe because it contains project settings you'd need if you wanted to rebuild. But you'd need the keyfile too.

BTW, if you do a "manual" deploy with CREATE ASSEMBLY only the actual binary is cataloged, of course. You can add the rest of the stuff manually with “ALTER ASSEMBLY ... ADD FILE FROM ...”. Or add any other file that strikes your fancy, for that matter.

After lots of interesting (but fruitful) experiences with the XQuery engine in SQL Server 2005, I decided that I'd rather have an XML schema collection for everything than put [1] (that's the numeric predicate meaning "item 1 using 1-based ordinals") everywhere in the XQuery that I needed a singleton. It's a good idea to use "typed XML" anyway. Dan Sullivan thought a good non-trivial use-case would be Office 2003 docs and went forth to create SQL Server 2005 XML schema collections from the publically available schema documents.

First thing he ran into is that specifying processContents="lax" now causes an error when cataloging the XML schema collection. It used to catalog without error and then use processContents="skip" behavior instead in beta1. The Office schemas use this construct, so he's created some code to change it to processContents="skip" so SQL Server 2005 would "like" it. We've run into a couple more cataloging problems, repeated attributes on the same element, etc... Wonder where you report Office schema problems.

If you're wondering what this is about technically, pulling out my favorite XML Quick Reference (by Aaron and Gudge), they define processContents (on xsd:any or xsd:anyAttribute) as "Specifies whether a schema processor should validate the elements/attributes appearing in place of the wildcard". The default is "strict", meaning validate them. Lax means the schema processor *should* (but is not required to) validate if schema information is available. Skip means a schema processor should not validate these.

BTW guys, when's Essential XML Quick Reference Volume 2 due out? There *lot's* more specs now. Maybe you could publish an Essential XML Reference Encyclopedia?

With the introduction of the XML data type (using an XML data model) comes there's a choice of pulling XML out of the database as a stream or column. Using an XML data type as a column type will get you a column of type XML. That's SqlXml to you in .NET. SELECT ... FOR XML gets you a stream. In SQL Server 2005 there a new keyword TYPE on SELECT ... FOR XML that makes the stream into an XML type. Some/most tools make the stream look like a column with up to a size of 4k per "row". SSMS in SQL Server 2005 does better with it than Query Analyzer.

There's different APIs for these too. In ADO.NET there is ExecuteXmlReader vs ExecuteReader/ExecuteScalar. In OLE DB you use a COM IStream implementation, there was a Stream class added to ADO classic. Although you can use the either API for stream or column, it appears to be a stretch to use “other one“ in both cases. As an example, using a 'SELECT xmlcol from xmltab' with ExecuteXmlReader only gets you the XML in the first row. As you'd expect, if you remember ExecuteXmlReader returns a singleton like ExecuteRow in ADO.NET 2.0 does. At this point, I'm recommending using the "right" API for the representation. And you can always translate to a string. And, using the client XML APIs, any of the supported XML data models (such as document or Infoset).

SQL Server 2005's HTTP endpoint functionality uses http.sys, the HTTP stack in the kernel, to do it's processing. Until lately Windows Server 2003 was the only OS to implement http.sys and HTTP endpoints only worked when SQL Server 2005 ran on this OS.

Windows XP SP2 shipped with http.sys and HTTP endpoints are now enabled on this OS as well. There is a difference between the two OSes that makes this feature subtely different on each one. The difference is in the version of IIS that runs on each OS. If you are running IIS at the same time, XP SP2 runs IIS 5.1 and Windows Server 2003 runs IIS 6.0. The difference is that IIS 5.1 does not use http.sys even if it is available and therefore grabs all of the HTTP traffic on whatever ports it's listening on. In most cases this is port 80 and/or 443. This interferes with SQL Server HTTP endpoints trying to use these ports. Dan Sullivan confirmed this when running the class on his laptop running Windows XP SP2 last week.

There are a couple of ways to get around this. You can specify a port other than port 80/443 for SQL Server's HTTP endpoints and run both endpoints and IIS at the same time. Another workaround is to shutdown IIS and only run SQL Server's HTTP endpoints.

Some folks have asked why I appear to use the XQuery data() function on occasion, but not always, when I'm doing demos. If you want the atomic value of an attribute, rather than the attribute node itself, data() is your friend. data() makes an atomic value from any XQuery item, but with attribute nodes it's particularly useful. SQL Server's XQuery functions don't allow returning bare attributes at the root level (bare text nodes are allowed), and the value function is always looking for a singleton atomic value. The data() function helps in these situations; here's an example:

declare @x xml
set @x = '<foo bar="baz"/>'
-- returns error: "Attribute may not appear outside of an element"
select @x.query('/foo/@bar')

declare @x xml
set @x = '<foo bar="baz"/>'
-- returns baz
select @x.query('data(/foo/@bar)')

Using the data() function is different from using the string() function. data() takes a sequence of items (nodes or atomic values) and atomizes them, ie. returns a sequence of atomic values. string() returns the string value of a single item.  Here's an example comparing data() to string():

declare @x xml
-- XML fragment
set @x = '<x>hello<y>world</y></x><x>again</x>'
select @x.query('data(/*)')

returns a sequence of two string values:
helloworld again

declare @x xml
-- XML fragment
set @x = '<x>hello<y>world</y></x><x>again</x>'
select @x.query('string(/*)')

returns a static typing error, because string() requires a singleton or empty sequence as input

declare @x xml
-- XML fragment
set @x = '<x>hello<y>world</y></x><x>again</x>'
select @x.query('string(/*[1])')

returns a single string value:
helloworld

I'll have more to say about static typing in SQL Server 2005 in the future. It can seem like it makes simple queries, where you “know” the data but don't use a schema harder to write, but I'm getting to like it.

After getting a laptop with enough power this summer, I'd taken a week or so to familiarize myself with Virtual PC. Using it is a great way to run multiple versions of operating systems and databases for testing. It only took a day to realize that a mere 768meg of memory wasn't going to be enough, so now I'm upgraded to 1.5 gig; haven't gotten the second hard drive for perf yet. Only hiccup is an extreme speed problem with XPSP2 *guest* OS, but I'm running XPSP2 host OS with no problem. There is a VPC service pack due out to fix the XPSP2 guest problem, I've heard.

Last week I had occasion to install Virtual Server to run a preconfigured domain for an assignment. I was happy to find that you can install VPC and VServer side-by-side, that the .vhd files for the main drive are more or less compatible and the concepts of differencing and undo disks are the same. Got networking with host using the loopback adapter as usual and I'm running a whole mini domain on laptop. More secure that VPC, but I do miss Shared Folders (I have to use network shares to copy files to/from the host). Also seems a tad more sluggish than VPC, but I don't have multi-processor or any other cool hardware that VServer is better suited for.

Having been around when the bank I worked at started running IBM's VM operating system (back when computers where coal-powered), I was naturally leery of incompatibility problems for guest OSes at the DBMS or other application level. So far, haven't seen any. Got over my original unease.

Now back to SQL Server 2005...

There are a number of different ways that SQL Server 2005 checks to see whether on not you're trying to catalog or execute dangerous .NET code. This checking is enforced by SQL Server at CREATE ASSEMBLY time and also at execution time.

During CREATE ASSEMBLY, SQL Server reflects on the assembly and check for certain coding practices than can "cause problems" at runtime. It's well-known that SQL Server does not allow you to catalog an asssembly as SAFE or EXTERNAL_ACCESS if the assembly contains finalizers or mutable statics.

It's also well-known that SQL Server will throw a SecurityException at runtime if you attempt to access classes or methods marked with a Host Protection Attribute. HostProtectionAttribute was added to the .NET 2.0 runtime to allow class-library authors to annotate their code in order to inform the host (in this case SQL Server) of behaviors such as "MayLeakOnAbort" or "SelfAffectingThreading". HPAs provide protection at execution time, not catalog time.

It's a little-known fact that SQL Server also checks for the presence of certain attributes on assembly code at CREATE ASSEMBLY time. If your code contains dangerous attributes CREATE ASSEMBLY does not succeed. This is different checking than what HPAs allow. This list of "dangerous attributes" is checked at catalog time. The attributes SQL Server considers dangerous include:
  System.STAThreadAttribute and System.MTAThreadAttribute
  System.ThreadStaticAttribute
  System.ContextStaticAttribute
  System.DllImportAttribute

Doubtless there are others. Attempting to catalog an assembly that contains one of these attributes produces an error message like this:
Msg 6237, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because method 'ProcWithAttr' on type 'StoredProcedures' in assembly 'SomeAssm' has invalid custom attribute 'System.STAThreadAttribute'.

Interestingly, I was able to find one that slipped by; SQL Server will allow cataloging an assembly with the UnverifiableCodeAttribute as SAFE. Maybe it was due to the fact that my module did not actually *contain* unverifiable code, just an attribute that said it did.

I'm amazed at the thoroughness with which SQL Server checks for code that can cause it problems as a .NET runtime host. .NET reflection is truly its friend; try to implement this type of checking on an extended stored procedure!

Because of SQL Server 2005's support of XQuery, a lot of folks have been asking about good XQuery-specific books. I've found two excellent books, each with a different focus.

"XQuery From The Experts" is a compendium of essays by the folks in the W3C XQuery working group. It's an excellent treatise of how XQuery is meant to work, the problems its meant to solve, and the details of implementation. Since most of the writers are also XQuery processor implementers, you're getting the "inside story". I especially like Jonathan Robie's XQuery tutorial chapter and Michael Rys' chapter on implementing an XML data type. Michael's XML data type implementation theory is not identical to the SQL Server 2005 implementation (although he's the PM for this feature), but its close enough in concept to make it a great read if you want to understand this feature.

If you're more interested in XQuery from a user perspective, Michael Brundage's "XQuery: The XML Query Language" is right up your alley. Michael covers the syntax and usage in depth, with lots of examples. The XQuery specs lack all but the most cursory examples (it's not their charter) so Mr. Brundage's book is a must. Although Michael works at Microsoft, his book doesn't cover the System.Xml implementation in .NET 2.0, but the language as defined in the spec. So you'll be doing deltas with the SQL Server 2005 or System.Xml implementations (which are closely aligned) in any case.

Remember that both books are based on a standard-in-progress, although now it's close to done. Happy XQuery-ing.