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.

There's a new Community Technology Preview of SQL Server 2005 available on MSDN for universal subscribers. I'm sure you've all heard of it by now, I was out of the country last week with limited bandwidth and just downloaded mine yesterday. I was meandering through the BOL, looking for interesting things, when it dawned on me that the BOL title bar read "Microsoft SQL Server 2005 Beta 3". Oh.

I'd heard rumors that the SQL set operations INTERSECT and EXCEPT might be added in beta 3. So I went to my handy BOL Search tab (it took some time to get used to Search being a Tab in the main page), and entered INTERSECT. They were doc'd! Cool.

Brought up SQL Server Management Studio and hammered out my example tables for the test. Then entered:

SELECT id FROM table1 INTERSECT SELECT id FROM table2

drumroll...answer is: "Incorrect syntax near the keyword 'intersect'."

Darn...maybe next CTP release. Either the rumor was mistaken or BOL is just a wee bit ahead of the implementation/testing. It happens.

For more on INTERSECT and EXCEPT check out the BOL. Or your E.F. Codd or ANSI SQL-92 book. Looks like it will be coming. For those of us that remember set theory (I studied it in the public school system in the 60s in 4th grade, does this fact date me?) it's how sets "always worked". For SQL relational calculus afficiandos we've been missing these keywords, although it was relatively straightforward to get the same results with using EXISTS and NOT EXISTS. Yet another feature from the standard that's implemented. I'm sure there's more to it than that, such as interesting questions like "can I use INTERSECT and EXCEPT with indexed views? or in common table expressions? or updateable ADO recordsets?"

So in this version of SQL Server there's the complement of relational calculus operations in T-SQL. And with .NET procedural code, you can whip up UDFs with formulas from differential and integral calculus that run fast, too. [sorry. couldn't help it]

A few weeks ago, I was surprised by an error message when attempting to create an HTTP endpoint with CREATE ENDPOINT. The error was "You do not have permission to perform this operation". The reason I was surprised was that SQL Server 2005 was running in a domain environment and I was logged on as domain administrator at the time. This meant I had sa-level database privledges, and privileges on the OS as well. Hmmm...

The reason for this turned out to be pretty straightforward. I was running the SQL Server process as a relatively unprivileged account, principal of least privilege and all that. When you create an HTTP endpoint, SQL Server issues a "namespace reservation" for part of the HTTP namespace. The reservation is used when other applications (like IIS 6.0) use the HTTP.sys implementation at the same time. It attempts to issue the reservation *using the identity of the principal this is running the SQL Server process*, not as your currently logged on user.

The way to accomplish the reservation under these conditions is to use a system stored procedure, sp_reserve_http_namespace.
It looks like this:

sp_reserve_http_namespace N'http://mymachine.mydomain.com:80/mydir'

Note that in order for this procedure to work, you must be logged in to SQL Server as a Windows login that has OS admin privileges. And so I was, and it worked. So did CREATE ENDPOINT... FOR HTTP. However I noticed that, in my CREATE ENDPOINT DDL statement I had to use the exact machine domain name for the SITE operand, rather than the default ('*' which means "use all machine names not otherwise reserved"). Oh.

That had everything to do with my input to sp_reserve_http_namespace. Using:

sp_reserve_http_namespace N'http://*:80/mydir'

instead, set things up so that I could use '*' as a SITE operand. Whew.

About a month ago, I'd written about unhandled errors in .NET procedural code always causing error 6522 to be returned to the (T-SQL) caller (see "Error 6522 and You"). Because the "real" error (the one that caused the .NET code exception) is wrapped in a 6522 error, retrieving the value of @@error will always give you 6522, not the real error. The canonical example is:

public static void Proc1
{
 SqlCommand cmd = SqlContext.GetCommand();
 // causes error 547 - reference constraint 
 cmd.CommandText = "delete authors where au_id like '1%'";
 SqlContext.GetPipe().Execute(cmd);
}

Called from T-SQL:

execute proc1
select @@error

The error you get is:

Msg 6522, Level 16, State 1, Procedure CauseError, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'CauseError':
System.Data.SqlServer.SqlException: DELETE statement conflicted with REFERENCE constraint 'FK__titleauth__au_id__0AD2A005'. The conflict occurred in database 'pubs', table 'titleauthor', column 'au_id'.

@@error = 6522

This week one of the students in SQL Server 2005 class discovered a neat workaround. Wrap the .NET code in a try-catch block and do nothing in the catch block. Like this:

public static void Proc1
{
 try
 {
   SqlCommand cmd = SqlContext.GetCommand();
   // causes error 547 - reference constraint 
   cmd.CommandText = "delete authors where au_id like '1%'";
   SqlContext.GetPipe().Execute(cmd);
 }
 catch { // dummy catch block }
}

This produces the expected error. And the expected value of @@error:

Msg 547, Level 16, State 0, Line 1
DELETE statement conflicted with REFERENCE constraint 'FK__titleauth__au_id__0AD2A005'. The conflict occurred in database 'pubs', table 'titleauthor', column 'au_id'.

@@error = 547

I'll have to try this in some other example exceptions, but it seems to do the trick in this one. Thanks to Bertil Syamken for the suggestion.

The new SNAC (SQL Native Client) OLE DB provider handles naming of parameters a tad differently than SQLOLEDB. Let's say that I have the parameterized query (using the ODBC-style question mark parameter markers):

SELECT * FROM authors WHERE au_id LIKE ?

And I call OLE DB's ICommandWithParameters::SetParameterInfo to set some parameter information. SetParameterInfo takes a parameter name as one of the parameters. In the case of the question mark parameter, what should I put for the name. Using the SNAC OLE DB provider and using a dummy name (say..."Dummy") produces the error:

DB_E_BADPARAMETERNAME - "Parameter name is unrecognized"

In SQLOLEDB the error is not produced. Difference between providers.

Looking at the OLE DB specification, although it's different behavior, it appears that the SNAC provider is "more correct". Here's the first part of the description of DB_E_BADPARAMETERNAME.

DB_E_BADPARAMETERNAME
In an element of rgParamBindInfo, the pwszName element specified an invalid parameter name. The provider does not check whether the name was correct for the specified parameter, just whether it was a valid parameter name.

SQL Server supports named parameters and positional parameters. Positional parameters have no name. Using a null pointer as the parameter name works, both in SNAC and SQLOLEDB. If you use named parameters in a SQL statement the parameters must start with an at-sign (@) like this:

SELECT * FROM authors WHERE au_id LIKE @auid

So valid values for SQL Server parameters are "null pointer" (no name) and names that start with at-sign. Names that do not start with at-sign SHOULD produce an error. Perhaps SQLOLEDB's forgiving behavior was to accomodate conversion from the OLE DB provider for ODBC (MSDASQL), where anything could be used as a parameter name. Nonetheless, it appears that SNAC provider's behavior is "more correct" they are checking that parameter names begin with at-sign.

Interestingly using either null pointer or @anyname as a parameter name works (ie SetParameterInfo does not produce an error) when you use SNAC. Even if its the wrong name. Note the spec says "The provider does not check whether the name was correct for the specified parameter, just whether it was a valid parameter name." Correct behavior here too. Although if you are using named parameter and use the *wrong* name, SetParameterInfo will work but ICommand::Execute will fail.

So, there are a few provider differences that I've found so far. If you're writing a new program or library, you should start with SNAC provider. To use the new features in SQL Server 2005 like MARS and Query Notifications, you MUST use SNAC (or SqlClient and ADO.NET of course). But be sure to test existing programs before making the switch.

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