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...