The code for the answer is below. Greg Low is, of course, correct. Fred owns the table, but he owns it by virtue of being the schema owner. Now here's part 2. Do the following:

1. Alter the authorization on Ed's table so that it is owned by Ed.
   (Interesting aside, can Ed do this himself?)
2. Create another table (using either Fred or Ed) in the schema.
   Call it "fredstuff.table1" (my creativity for making sample names is legendary)
3. Alter the schema so that it's owned by another user (say, dbo)

Who owns each table now? How can you tell?

BTW, why does this matter? Because ownership chains go by object *owners*, not by schemas.

-- snip --
create database test
go

use test
go

-- make two users
-- fred has a default schema, ed does not
create user fred for login fred with default_schema = fredstuff
create user ed for login ed
go

-- create the schema for fred
create schema fredstuff authorization fred
go

-- fred and ed can create tables
-- ed can only create tables in fred's schema
grant create table to fred,ed
grant alter on schema::fredstuff to ed
go

setuser 'ed'
go

-- ed creates a table in fred's schema
-- who is the owner?
create table fredstuff.edtab (id int)
go

-- fred (schema owner) is the owner. not ed.
-- ed cannot even SELECT against the table he just created, this fails
select * from fredstuff.edtab
go
-- snip --

UI can't stay away from the separation and users and schemas feature. I want to make sure I have it cold, and following up on information I got from Girish Chandler's talk at Win-Dev, I did the following experiement. Posted in the form of a multi-part puzzle. Answer to the first part (with code) tomorrow.

1. You have two logins, Fred and Ed. Neither one has any special privileges
2. You create users for them in a database:
   Fred has a default schema of Fredstuff, which he owns
   Ed has no default schema
3. You grant both of them CREATE TABLE
   And Grant Ed ALTER priviledge on the Fredstuff schema (this lets him CREATE and ALTER objects in the schema).
4. Now Ed issues a CREATE TABLE statement to create a table in the Fredstuff schema

Who owns the table?

When you retreive a rowset that contains an XML data type column or use an XML data type column as parameter input, you have two choices. You can use the vanilla .NET type, System.String, or a special types from the System.Data.SqlTypes namespace called SqlXml. ADO.NET 2.0 handles conversion when you use strings (as long as they're valid XML, naturally). SqlXml has a method that produces an XmlReader and a constructor that takes XmlReader as input. Richer datatype.

Recently I wrote code that used an XML data type as an output parameter from a stored procedure. I specified the output parameter as SqlDbType.Xml and went to cast the result to the rich SqlXml type...invalid cast. Output parameters appear to always be returned as System.String or System.Data.SqlTypes.SqlString, NOT SqlXml. After the call, the SqlDbType is still reported as SqlDbType.Xml. *Input* parameters work fine as SqlXml, parameters with Direction InputOutput or Output are SqlString. Hmmm... Let's see if the upcoming Community Technology Preview, due out soon, helps this.

I returned home from Europe late Saturday night. With catching up on my sleep, email, questions, Thanksgiving (which Mary and I celebrated on Sunday this year), and everything else, I just realized its Tuesday morning and no blog entries. Time to blog again. I had two great classes, the caliber of the students was outstanding (you know who you are). One was given at Trivadis in Zurich, they were great hosts...thanks for having me over, folks. On the way back, I was actually upgraded to first class on transcontinental flight in airplaneland. You know, the one with the little pods and "bed chairs". Perhaps I'm too easily impressed.

A few weeks ago, I'd posted links to ADO.NET 2.0 articles I'd written for MSDN online. At that time, I mentioned there would be one more article in a series, but I'd save the subject as a "surprise". Well, the article just appeared: Tracing Data Access in ADO.NET 2.0.

Did you know there was built-in tracing in ADO.NET 2.0 and SQL Native Client? Were you surprised?

Also there's another nice whitepaper on when to (and when not to) use SQLCLR in SQL Server 2005. By the folks who brought you both SQLCLR and T-SQL enhancements in the upcoming new SQL Server release.

I was demonstrating SQLCLR appdomain usage (see previous post) to a class last week.Later on, I mentioned a different concept, that of dependent assemblies. This brought up the following question:

If user A owns assembly A and B owns assembly B, what happens if B contains a routine that calls A? Are two versions of assembly A available, owned by different owners? Or does this situation produce an error at CREATE ASSEMBLY time? Or at runtime?

The answer is that CREATE ASSEMBLY fails for assembly B, but with a fairly surprising error. The error is:

Msg 6218, Level 16, State 2, Line 1
CREATE ASSEMBLY for assembly 'Depends1' failed because assembly 'B' failed verification.  Run peverify on the assembly to determine the cause of failure.

Of course, running PEVERIFY on B works correctly, the error indicates that the dependent assembly 'A' could not be loaded into 'B's appdomain. This causes CREATE ASSEMBLY to fail.

It appears that you cannot work around this even by cataloging multiple versions of the assembly A, one version owned by A and the other owned by B. Even if there are multiple versions of the assembly in the database, SQLCLR only attempts to load the version that is cataloged so that the SQL Server name matches the name in the assembly manifest. As an example, even if I create assembly A owned by B and name it A1, attempting to catalog B fails with message 6218 above.

So if assembly A calls a method in assembly B, A and B must have the same owner.

A little-known behavior of SQLCLR (although we did mention it in our book) is that SQLCLR creates one appdomain per assembly owner, not one appdomain per database. One appdomain per database was the observed behavior in beta 1, although its important to remember that the algorithm for appdomain creation/destruction in SQLCLR is technically undocumented and subject to change.

This is NOT one appdomain per user, but one appdomain per assembly owner. This means that if user A owns assembly A and user B owns assembly B, A and B load in separate appdomains. Since remoting is not currently supported using SQLCLR, this makes allowing A and access B's classes and methods problematic. One workaround is to ensure that all assemblies that wish to share are owned by the same user, role, or application role.

BTW, one way to "see" the appdomain usage is to have a look at the SQL Server log. This contains messages for each appdomain creation and unloading.

This is actually posted a few days after I wrote it. I haven't done the “internet on the plane” service although I think it exists.

5:00AM Paris time, in the skies over ????

Right now, I'm in an alternate reality space that I call "AirplaneLand". I've racked up over 100,000 "airplane miles" this year, which is an indication of how much of my life is spent in this space. For instance, today Mary and I left the house at 5:45AM, she dropped me off in AirplaneLand at 6:15AM PST (I count the airport and other ancillary locations as part of this space) and when I arrive in Paris it will be 9:30AM the next day. That's over a day gone. Granted, some of that is due to timezone difference (I lose 9hrs going) but this still is a significant chunk of time, if you add it all up.

AirplaneLand is a life that consists of (too much) waiting in lines, hanging out at Red Carpet Club and gates, and, of course, the plane itself. The plane is the location where time expands and contracts. Oh yes, the Red Carpet Club. If you spend a LOT of time in AirplaneLand you are rewarded and treated better than everyone else. It's a amazingly intricate caste system; this evening at dinner, I got to request my dinner choice before almost everyone. I think they actually go by yearly milage. That's cool, I'm a "AirplaneLand resident" after all. Similar to the difference between "resident" and "non-resident" lines when checking through customs.

AirplaneLand has its own set of rules, especially lately. Today was my first look at the gate that they have to block people so pilots can go to the bathroom. 2 of use actually asked if it was electrified. With a straight face. My least favorite is the "use the bathroom of your own caste only" rule. The planes weren't configured for this, the bathrooms are in the wrong place. Everyone breaks this rule with impunity, but I seem to be one of the few who gets physically blocked by the airplane personnel. I was "less than thrilled" at the selective enforcement when this happened recently. It was empty, I was sitting immediately next to the "wrong" one and lots of folks had just tripped over me to get there (and they were the wrong caste too). Seemingly little things mean a lot in AirplaneLand.

Oh well, I told folks I'd write about my "second home" and that's it. In about 3 hours they wake us up and serve us "breakfast". Don't know where the day's gone...well, OK, I do know.

SQLNS is an acronym for SQL Server Notification Services. Originally introduced as a "free addition" to SQL Server 2000, it is an integrated part of SQL Server 2005. SQLNS is a framework for a specific kind of application, a publish-subscribe notification application.

We did a chapter on SQLNS 2005 in our "First look at SQL Server 2005 for Developers" book, but came up about 400 pages short of doing it justice for the true afficianado. Recently I'd heard about a few SQLNS-specific books being released, one by an acquaintance of mine from the SQLNS development team, Shyam Pather. Devs don't usually wrote books, and I just had to read this one. I just finished reading Shyam's amazing "Microsoft SQL Server 2000 Notification Services" from Sams press. It's just as good as I thought it would be.

I first met Shyam, Tete Mensa-Annan, and the other members of the SQLNS team when I spent a few days wallowing around in their knowledge of the product, in preparation for writing a DM course on the subject. They wanted to get developers and consultants up on the technology quickly. As far as I could determine, Shyam worked on the SQLNS engine itself, he and Phil Garrett and others spent a few hours with me a conference room, explaining the intriquicies of what they called "quantum theory", that is, how the SQLNS scheduling and execution engine service works. Tete spent a few hours explaining how SMS protocol and aggregators work in detail. I was amazed at the depth and bredth of their knowledge of the subjects. I could hardly write/type/think fast enough to keep up.

Back to the book. It's 600 pages of "SQLNS as a way of life", from beginning to end. It explores setup (without any gratuitous screen shots of setup, THANK YOU), configuration, programming instances and applications, writing the processing configuration files, the subscription management application. There are chapters about custom event providers, formatters, and delivery protocol; there is an example of each. But of course, where this book shines most (is shine most the correct construct? hmmm...) is in the description of internals, tuning, and troubleshooting. That's where it's invaluable. I still remember going over "quantum theory" and all its permutations when I read that chapter.

If you use SQLNS as a notification application framework, it's very easy to get a first instance working, but when your app gets popular and you need 10 more just like it immediately, working with SQLNS quickly becomes all-consuming, that's what I meant by "SQLNS as a way of life". If you're looking to "check out" SQLNS or see if its "right" for your application that's fine too, the preface even gets you quickly up to speed on the SQLNS lingo. I also remember Shyam writing to me a few times about the "its not a custom delivery channel, its a custom delivery protocol". In any case, there's *no way* you can be dissapointed with this book. I was so happy with it that I snagged a few copies to give away at the Guerilla SQL Server in Redmond. And Tete will be there to answer questions and help with SQLNS 2005 after the module/lab on it.

Every time I demonstrate using XQuery against a table that contains an SQL Server XML data type to a new class, a get about one out of four people with puzzled looks. And they're the ones who know XPath and XSLT. The part that seems weird to them is that a XQuery against what they see as "a collection of documents in a table" produces a one-row answer for each row in the table. You can tell they were expecting a single document (or fragment) answer. So,

SELECT xmlcol.query('/somequery') FROM xmltable

produces a rowset with a single column; there is one row in the "answer" for each row in the table. Eventually someone pipes up with: "well, I want just one answer". Can't you do that?

You can actually get just one answer in a few different ways. The easiest is to query the entire table using SELECT...FOR XML and do the query on the result:

DECLARE @x XML
SET @x = (SELECT xmlcol from xmltable FOR XML RAW, TYPE)
SELECT @x.query('/somequery')

The only problem you can run into with this is the maximum size of an SQL Server XML data type instance, which is ~2gig. You also have to take the "extra" elements that FOR XML generates into account in your final query. You can also do the original XQuery, then do a FOR XML to combine the results together.

But, you can even cut it finer than that. Using an XML.nodes query with CROSS APPLY or OUTER APPLY, you can generate one row in the answer for each node in the sequence that XML.nodes returns (including or ignoring empty-sequence rows, that's what CROSS vs OUTER apply does). These "intermediate rows" from XML.nodes actually contain "virtual documents" with the context node (starting point of the XQuery) possibly pointing somewhere other than the root. You then do XQuery against the virtual document on the left-hand side of the CROSS APPLY. Like this:

SELECT custid, tab.col.query('itemno') --get itemno subelement of lineitem
  FROM xmlinvoices
  CROSS APPLY (SELECT * FROM
    xmlinv.nodes('//lineitem')) as tab(col)

In this case, if you have a 10-row table, but the 10 XML documents in the table contain 42 lineitem elements, you get 42 rows in the answer. Cool 'eh? If you currently use OPENXML, learn XML.nodes, because this will eventually replace OPENXML in our toolbox. It's MUCH less memory-intensive than OPENXML.

Finally (someone asked this yesterday), if you DO want to start with 1-row/answer per row in table and just throw away the rows that contain empty sequence answer, a simple subquery will do:

SELECT * FROM
  (SELECT xmlcol.query('/somequery') AS col FROM xmltable) AS x
  WHERE col <> ''

There. Exactly the answer you want...whatever the problem. Have fun.

Looking for some cool SQLCLR and/or security-related features to show off. Before I hit the road again Friday, for a long stint in “airplane-land“ on way to Europe. Browsing through the SqlServer provider in Community Preview beta version of SQL Server 2005, I think I've found one. Came across SqlContext.GetWindowsIdentity.

With this call you can find out the Windows identity of a code's executor (remember procedures execute as caller by default) and also impersonate the caller (in SQLCLR) for the purpose of calling outside or inside the SQL Server process. By default, impersonation does not occur, this is a way to specifically make it happen. Only appears to work if assembly is cataloged as UNSAFE, though it would appear that it could be useful in EXTERNAL_ACCESS assemblies as well. Subject to SQL and Windows permissions.

It's even more interesting when used in conjunction with a SQL Server Login (which has no Windows credentials to speak of) and mapping Windows credentials to a SQL Server login with CREATE CREDENTIALS/ALTER LOGIN. The credential mapping appears to be a way to allow SQL logins to have an identity (all managed by the DBA and system administrators of course) in the underlying operating system.

[Comment heard from an XML afficianado] A: Uh...Yes, lots of them...

User-schema separation always leads to the recollection that "user-schema separation is the way things are defined in ANSI SQL 1999". Which brings up the subject of standards. Touting the ANSI SQL 1999 standard is passe now, because the ANSI SQL 2003 is out. And SQL 2003 "supercedes and obsoletes all previous ANSI SQL standards", the standard itself actually states this. Wow, does this make SQL 1992 twice-obsolete then?

Couple of points distinguish between the SQL and XML standards:
1. XML standards are usually hammered out (well version 1.0 is) before there are any/many official implementations to have "backward compatibility" issues. SQL standards began in earnest after the big players implemented RDBMS. ANSI SQL standards are much less rigidly followed.
2. SQL standards have a notion of "partial compliance", levels of compliance, and optional features much more than XML standards do. Both leave things open for "implementation dependent".
3. XML standards are freely posted on W3C website, though you must pay money to join the W3C. ANSI SQL, along with other ANSI standards are available at cost. Hmmm... what does this indicate? Dunno.

Standards commitees are fairly political (that's an understatement). There's a story that the original ANSI database standard committee was supposed to hammer out a standard for CODASYL databases, and RDBMS companies "stole the show". That's probably the tip of the iceburg.

For your edification and enjoyment, here are the official parts of the SQL 2003 specs:

— Part 1: Framework (SQL/Framework)
— Part 2: Foundation (SQL/Foundation)
— Part 3: Call-Level Interface (SQL/CLI)
— Part 4: Persistent Stored Modules (SQL/PSM)
— Part 9: Management of External Data (SQL/MED)
— Part 10: Object Language Bindings (SQL/OLB)
— Part 11: Information and Definition Schema (SQL/Schemata)
— Part 13: Routines and Types Using the Java™ Programming Language (SQL/JRT)
— Part 14: XML-Related Specifications (SQL/XML)

Part 14 is new. And is the basis for SQL Server 2005's XML data type. There are also parts (XA was part 6, I believe) that moved on to its a different spec series. And SQL/MM (multimedia, used to be part 8?) that has its own whole set of specs now. As does SQL Temporal (time series).

I wonder what happened to Part 5 - SQL Language Bindings? And I always wonder why parts 10 and 13 are couched entirely in terms of a programming language that is not itself an ANSI or even ECMA standard, but a "de facto" standard (rather than a "de jure" standard). Oh well...

The coolest SQL standard books are Jim Melton's two part series on SQL:1999. And SQL-99, Complete Really. Although they're both now officially obsolete (wonder if they'll be a SQL-2003 Complete Really?). Enjoy.

More on user-schema separation. In SQL 2000 and previous versions, granting someone CREATE TABLE privilege meant that they could create tables (no surprise there). The tables were "named after them" (e.g. bob.sometable) unless they were DBO. Because of user-schema separation in SQL Server 2005, that's no longer accurate.

I always explain this in terms of what you may not have the ability to do. And that, unless you have a default schema, your default schema is DBO, which you probably can't write to. There's a more polite/positive way of expressing this, courtesy of Dan. To be able to say...create tables, you need:
1. CREATE TABLE permission
2. A schema in which you are allowed to create objects. Or a schema that you or one of your roles own.

BTW, if this doesn't jibe with your experience, you're probably using the GUI (SSMS) or the legacy system stored proc sp_adduser, which creates a schema for you (for backward compatibility) rather than the new, cool (is security cool? yes, I think so), DDL statement CREATE USER. If you're using SSMS, to see what I mean:
1. Go to Security/logins in Object Explorer.
2. Choose to create a new login. Type in a login name/password
3. Click on database access
4. Permit access to a database by checking the Permit checkbox
5. Note that "default schema" and "user" get filled in with your userid
6. Click in the "default schema" cell. Oh.

NOTE that *default schema is a dropdown list*. You get to choose your default schema, or choose not to have one at all, in which case it's DBO.

People always ask...if ownership chains work the way they do, why do they not work with dynamic SQL? And how about .NET procedures and ownership chains?

Dynamic SQL is supported in nearly every database I've run across, but bad dynamic SQL has "issues", to put it mildly. If you create your dynamic SQL via string concatenation rather then parameterization (there are parts of the SQL statement that cannot be parameterized) and your strings come from user input, you will run up against a security problem known as SQL injection. Users can enter strings in applications, that, when used with concatenation, do things that you never intended your statement to do. You need to be VERY aware of the hazards of SQL injection before even *thinking* about dynamic SQL.

SQL Server procedural code (stored procedures, UDFs, and triggers) runs as the caller of the procedure. Because dynamic SQL can be dangerous, it doesn't go by the ownership chaining rule. Access to database objects in dynamic SQL is always checked. Against the original caller's permissions. In SQL Server 2005, the EXECUTE AS clause can allow procedural code to run as a principal other than the caller, which permits a way to address this behavior (other than the usual way, which was to yell “don't use dynamic SQL“ loudly).

When .NET procedural code uses the SqlServer provider to issue SQL statements, these are *dynamic* SQL to the engine. Ownership chains do not apply. I've had difficulty using EXECUTE AS with .NET code in betas, hoping that the new betas fix this. This mostly matters for procedures and triggers. You usually don't do data access in UDFs and user-defined aggregates don't have an EXECUTE AS clause at all; you shouldn't be doing data access in UDAggs anyway.

Separation of users and schemas is another cool SQL Server 2005 feature, but it has some interesting behaviors that folks may have to get used to. One is database object resolution, another is ownership chains. Say I have a user FRED.

FRED is the owner of a schema named FRED
FRED is a memeber of the role PAYROLL
FRED's default schema is PAYROLL

Even though FRED is the owner of a schema named after him, resolving a one-part object name like some_table uses a simple_algorithm: look in default schema first, then look in dbo schema. Even if you own another schema, only your default schema is used to resolve a 1-part name. BTW, the "sys" metadata schema complicates this a little bit, but I'm ignoring that for now. So for FRED, if the following tables exist:

fred.some_table
dbo.some_table
payroll.some_table

the statement "select * from some_table" executed by FRED, selects payroll.some_table. If payroll.some_table is dropped, it selects dbo.some_table. If FRED leaves the payroll department (and is removed from the role), it still selects dbo.some_table. Only when you do:

ALTER USER FRED WITH DEFAULT_SCHEMA = FRED

will it even attempt to resolve the 1-part name to fred.some_table.

I'd always wondered about how this affected ownership chains, too. A simplistic explanation of these is: authorization of a database object is only checked when an ownership chain is broken. So if procedure A uses table B, authorization is only checked if the owner of procedure A is different from the owner of table B.

So does user-schema separation change this? Is "ownership" defined as the user who owns the object or as the schema the object lives in? This is an easy one also...owner is still not object's owner, NOT the schema the object lives in.

This can have some interesting twists because you can GRANT other users the right to create objects in a schema you own:

GRANT CREATE TABLE TO ALICE
GRANT ALTER ON SCHEMA::FRED to ALICE

means ALICE can create tables in the FRED schema. But that's a subject for another day....

I was browsing through some of the SQL Server 2005 code samples today (the ones that come with the product) and came across one that was a really nice idea. It's a library to encapsulate SQL Server Service Broker T-SQL calls in an object model, called ServiceBrokerInterface. Some of the other Service Broker samples are written using it. I've always told folks when they ask about using Service Broker on the client to use raw T-SQL from SqlCommand, but this is a nice wrapper. It can be used to write a client program that processes Service Broker messages or "service programs", that is, a stored procedure inside the server that is used as an activation procedure for a Service Broker queue. Works either way. Kudos to the SQL Server sample-writing team, and that's probably not the only gem in there. Reminded me of...

My all-time favorite SDK sample by far is the OLE DB Rowset Viewer. OLE DB is a fairly overwhelming API to most folks, with a large surface area. You could use Rowset Viewer to not only understand how the OLE DB spec worked, but how provider writers implemented edge case behavior. As an adjunct to coding it yourself, of course. You could work out a complex consumer-task (perhaps involving lots of related OLE DB properties) in the tool, and then go code it up once you "got the answer". Invaluable tool, amazing aid for experimentation.

And now, for something a little technically lighter... I've taken to using a convention when writing statements that involve XML/XQuery and SQL together. I put the XML input or XQuery program on separate lines than the SQL part of the statement, this seems to make both query languages and also inline XML data easier to read when they're in the same statement. Like this:

-- SQL part
SELECT invoice.query('
{-- XQuery part, smiley face comments still unsupported --}
declare namespace inv = "urn:www-develop-com:invoices";
for $i in /inv:Invoice
return <foo></foo>
{-- more SQL follows --}
')
FROM invoices

I don't necessarily use the XQuery comments at the end (or at the beginning either), but you get the gist. If you adapt this convention, don't EVER try this:

INSERT INTO xmltable VALUES('
<?xml version="1.0" encoding="UTF-8"?>
<doc></doc>
')

Looks like a variation of the first example, but it won't work. The ?xml declaration (it's not a processing instruction) must be *exactly* the first characters in an XML document if used (its optional). The parser uses the first few characters in the declaration, if you use it, to identify the XML document, realizing that the declaration also specifies the document encoding. "Pretty formatting" XML in the example above produces a carriage return-line feed followed by XML declaration, which is forbidden. Error is:

Msg 9438, Level 16, State 1, Line 1
XML parsing: line 2, character 6, text/xmldecl not at the beginning of input

Either this:

INSERT INTO xmltable VALUES('<?xml version="1.0" encoding="UTF-8"?>
<doc></doc>
')

or leaving the declaration out if possible:

INSERT INTO xmltable VALUES('
<doc></doc>
')

will work fine.

Rereading the post about this from last night, it appears that I may have used an ambiguous analogy when attempting to figure why this feature works the way it does, and given folks the wrong idea. It has to do with whether the results of the query would be wrong or the query itself is “incorrect”. Using the invoice example from the previous post:

-- this SQL query would fail to compile
SELECT invoice.query('
declare namespace inv = "urn:www-develop-com:invoices";
for $i in /fooelement
return <foo></foo>
')
FROM invoices

-- this SQL query would compile, execute,
-- and produce correct (but not schema-valid) results
SELECT invoice.query('
declare namespace inv = "urn:www-develop-com:invoices";
for $i in /inv:Invoice
return <foo></foo>
')
FROM invoices

The reason for this is that the SQL Server “query” method on SQL Server's XML data type always produces *untyped* XML by definition. So the first SQL statement fails because the query itself is wrong, not because the results would be incorrect.

In the analogy to SQL, the statement: “SELECT foo FROM invoices” fails not because there can't be a “foo” column in the output rowset but because selecting foo on the input in invalid. Actually, you can produce a “foo” column in the output:

SELECT invoiceid AS foo FROM invoices

so perhaps that wasn't such a bad analogy after all.

Dan Sullivan pointed this out to me a couple of weeks ago. I'd forgotten about it, but it came back to bite me in a demo last week. It's a feature.

If you have an XML column, variable, or UDF return value that's tied to an XML SCHEMA COLLECTION, any XQuery against that XML will be checked for compliance to the schema as part of SQL query parsing. That's right, *XQuery* code will be checked. Here's an example:

I have an XML SCHEMA COLLECTION named invoice_xsd that contains one XML schema that defines the types in an invoice. The schema contains no definition for a "foo" element, just "invoice-like things" like PartNumber and LineItem. And I have a table

CREATE TABLE invoices (xmlinvoice xml(invoice_xsd))

The following SQL statement:

SELECT xmlinvoice.exist('/foo') FROM invoices

produces an error at *query parse time*. Not the answer "false". The error is:

Msg 2260, Level 16, State 1, Line 2
XQuery: There is no element named 'foo'
Msg 9504, Level 16, State 0, Line 2
Errors and/or warnings occurred when processing the XQuery statement for xml data type method 'exist', invoked on column 'invoice', table 'invoices'. See previous error messages for more details.

The SQL query *doesn't even execute*.

Dan's original "proof" of this behavior was that this function doesn't even catalog, ie, CREATE FUNCTION fails:

CREATE FUNCTION DoSomethingWithInvoice(@a xml(invoice_xsd))
RETURNS int
AS
-- other code elided
DECLARE @x XML
SET @x = @a.query('/foo')

Now that's what I'd call *early* validation. I guess its the same as the fact that "SELECT foo FROM invoices" also fails with "invalid column name 'foo'" error. Very cool.

I seem to have lost the gift, if I ever had it, for what some of my old collegues would call (with a smile on their face) "shameless self-promotion". Last week at Win-Dev attendees at my talks pointed out to me that I didn't have a slide that advertised my email address, or web site of samples, or this blog. I didn't even have a slide plugging our SQL Server 2005 For Developers book, even. Sheesh... So I crufted up a slide for day 2. I even forgot business cards. Yep, really.

Reason this topic comes to mind is, when I mention you can find more information of some of the new SQL Server 2005, SqlClient and ADO.NET 2.0 topics in an article series I wrote for MSDN online (Data Access and Storage Center), folks ask for references. Here they are:

1. ADO.NET 2.0 Feature Matrix
2. Generic Coding with the ADO.NET 2.0 Base Classes and Factories
3. Edit/Build/Publish: Schemas in ADO.NET 2.0 - This is about DbConnection.GetSchema(), a.k.a. information schema functionality ++
4. Using Query Notifications in ADO.NET 2.0
5. XML Data Type Support in ADO.NET 2.0: Handling XML from SQL Server 2005

There's one more article in the series coming in another month or so. I'll keep the suspense going for now.

I've also built my "do-very-little-for-exposition" .NET Data Provider using the new ADO.NET 2.0 provider base classes (System.Data.Common and System.Data.ProviderBase) , a ProviderFactory, and provider “registration”. An article about the process of building got published in MSDN magazine, a few years ago; this is an update.

There. Was that too shameless for ya?

I've known for a couple of weeks now that XQuery and the new XML-SQL client mapping have been dropped from System.Xml in .NET 2.0. The XQuery implementation over the XML data type in SQL Server 2005 is NOT going away, of course. Just the client-side bits. Folks are encouraged to keep using XSLT 1.0 and XPath 1.0 on the client for a while. Hmmm...

After reading (more than twice) through all of the reasons for this decision, the one that makes the most sense to me is the product schedules. The reason that doesn't quite ring true to me is "folks are happy with the XML DOM". I can't help thinking that folks were quite happy doing everything through cursors in the early days of SQL because they didn't quite grok where the power of the relational model was yet. SQL cursors were "comfortable", as the XMLDOM is to XML programmers today. The schedule argument is more reasonable. If XQuery spec won't be "done" until next year, there is hesitation about producing an implementation based on an "in progress" spec that could change at the last minute. If you remember XSL Patterns and XDR schemas in Microsoft APIs you'll understand why. But...

This week at Win-Dev folks "across the hall" were lecturing in earnest about the WSE 2.0 offering. The WSE (web service extension) offerings are supported add-ons, outside of the "core" .NET APIs, and mostly implementations of various WS-* specs-in-progress. They're not guarenteed to be compatible with future offerings or with Indigo, the next generation WS-*++ implementation. In fact, some of the specs that were supported in WSE 1.0 have already completely vanished from the WS-* landscape. There's *way* more churn in this space.

Got me thinking...why not a similar model for XQuery? That is, a supported add-on implementation of the current specification with namespaces that begin with Microsoft.* rather than System.Xml.*. Guarenteed to change, at least subtly, but existing to get folks used to using it. The alternatives, that is, using Saxon.NET or working on a community implementation of XSLT 2.0/XQuery 1.0/XPath 2.0 are already happening. How about it...Microsoft.Xml.Query/Microsoft.Xml.Mapping anyone?

Got back from Win-Dev last night...and immediately crashed. It was a great show, a little exhausting because some of the folks that I'd lined up for the data track bowed out and I did some alternative talks. This is the first year Win-Dev had a database-data access centric track and, if the comments were any indication, folks liked the idea a lot.

Interesting thing happened at Win-Dev...because I made a comment about having worked with IMS and IDMS (well-known mainframe databases that do not follow the relational model), someone asked how old I was. I did the math (born in '53) and came up with 52 years old. Well...I'm really 51 and the point is that I'm doing computations as if the current year *is* 2005 already. Turns out that I've been teaching SQL Server 2005 and Visual Studio 2005 for a while and have started thinking it's already 2005. I live in the future, I guess.

Two more months and I'll be right...and sometime after (well, within the year) these products will ship. And I'll be able to compute my age quickly again.

One more thing about getting an error 6522 wrapper from SQLCLR procedures. the workaround I spoke about last week (dummy catch block) works to propagate SQL Server errors to the client without wrapping them in the 6522. But it doesn't seem to work if you want to throw your own user error (error > 50000). I've been throwing them by using a SqlCommand with the CommandText property set to "RAISERROR ....". You get 6522 wrapper here too.

This week I found out that you can lose the 6522 wrapper for your custom errors if you user SqlPipe.Execute on the SqlCommand with the RAISERROR SqlCommand rather than using SqlCommand.ExecuteNonQuery as I did. Thanks for Pablo Castro for this information. No word yet on whether either of these methods are the "official correct way" to accomplish throwing errors going forward.

Made it to this conference, came a day early just in case (see I'm at SQLPass...not). No hurricanes at this one, but fairly nasty cold and rain yesterday.

Conference is in Boston (well, actually Quincy, MA) this week. We have a database track this year, and I'll be doing a day of SQL Server 2005 / ADO 2.0 and some related talks as well. When the taxi pulled in to the hotel parking lot I was surprised to see a fleet of police motorcycles and a big crowd in the lobby. Rumor was that the baseball players were staying here since the world series is in town this weekend. Lots of partying after the games in any case.

The lobby was packed all weekend and since I saw someone signing autographs for a few children (this doesn't usually happen at software conferences that I'm aware of) looks like the rumors were true. Things ought to quiet down a bit since everyone's on their way to St Louis today. Or...all the action will be in the conference rooms rather than in the lobby.