About a month ago, there was another chapter in the long discussion about DataSets and web services. An old cohort Scott Hanselman fired off the first salvo, which was taken up by Ted Neward. Doug Purdy responds that Purchase Orders are root of all evil and lots of other chimed in. I'm just catching up...

This comes up again as I work with SQL Server 2005 web services. They too produce “SqlRowset == Schema + Any" in WSDL. Both XML and object afficianados tend to dislike this, but it exactly reflects the relational resultset, rowset, whatever you'd like to call it.

Relational results are columns and rows, where each row contains the same number of columns and each column contains the same data type for each row. Been around for a while now. The resultset is a "generic container class" or special 2-dimensional array if you want to think in those terms, in which each instance can contain different types, but the shape is rectangular. Resultsets, looking at an Open Data Services program which makes the protocol explicit, are preceeded by a sequence of describe packets that contain the name, data type, and other information about each column. So "Schema + Any" means exactly that. *Instance* schema, followed by the instance it describes. Corresponds quite nicely with relational databases, where the majority of corporate data is stored.

The problems stem from the fact that lots of web services toolkits, that are doing XML-to-Object mapping, don't recognize this paradigm. They are looking for an individual (static) schema, not an instance (dynamic) schema. In addition, the XML Schema spec states that how you find a schema from an instance of an XML document is implementation dependent. It mentions a hint (xsi:schemaLocation) and some location strategies for schemas with namespaces that are URLs, but says processors don't have to support the hint. Most WSDL that uses "Schema + Any" doesn't provide the hint. With Microsoft XML processors, this may be because "Schema + Any" is part of the implementation, an "implementation dependent" location strategy.

There's a few ways to resolve this, if you're looking for strongly typed objects at compile time. One way is strongly-typed DataSets, which fetch out the information at compile time, make a DataSet wrapper class, and, for web services, write WSDL that amounts to "Any from a specific namespace", and import the namespace schema, which includes the strong type. Another way is to write your own WSDL for each resultset.

If you write a stored procedure in SQL Server that looks like this:

CREATE PROCEDURE getauthors
AS
SELECT au_id, au_fname, au_lname FROM authors

you know exactly what that resultset will look like. Unless the DBA changes the column type, in which case you'll have other problems. Even if you use the "SELECT * FROM ..." concept (which is not usually recommended) this works unless the DBA adds or deletes a column, or changes the order of definition. I haven't been able to find a metadata table in SQL Server (or in the ANSI/SQL spec) that lists the shape of all resultsets returned by stored procedures (there is one for *parameter* information) , so you have to use your knowledge of the resultset to handcraft the WSDL. In ADO.NET, you can use the DataSet's "WriteXmlSchema" method as a starter. Or use "SET FMTONLY ON;SET NO_BROWSETABLE ON;EXECUTE...". SQL Server 2005 web services that map stored procedure invocation to web service methods will let you specify a custom WSDL-generating stored procedure. Statically store the metadata (instance schemas) in the database or dynamic generate them at WSDL generation time.

BTW, this mapping of resultsets to XML schemas amounts to relational-to-XML mapping. So web service toolkits can accomplish their XML-to-object mapping. I'll return to this theme in future...

I collegue of mine is studying SQL Server Analysis Services and asks about APIs for it. I have the unique (wierd?) perspective of having come at OLAP through the APIs: OLE DB for OLAP, ADOMD, and XMLA. However, he's interrested in .NET APIs and asks: “Can the OleDb data provider support the MSOLAP OLE DB provider”? My answer is “not exactly” with the qualification that the OleDb data provider doesn't support the OLE DB Dataset. Thought I've have to explain that one.

Back when I was teaching OLE DB in earnest, I took it upon myself to learn every one of the 60-some-odd OLE DB interfaces. Could tell you the difference between IRowsetView and IViewRowset. A varitable fountain of now-mostly-obsolete (unless you use linked servers or maintain OLE DB code) esoteric information. OLE DB for OLAP (and later OLE DB for Data Mining) added more interfaces and OLE DB for OLAP even added a new cotype called the Dataset. I taught a class to OLE DB for OLAP provider writers and learned these too. The OLE DB for OLAP Dataset is unrelated to the ADO.NET DataSet.

The OLE DB for OLAP Dataset is a refinement to the OLE DB Rowset, adding multidimensional features like multiple values per cell. This Dataset (lower-case 's') is what's not supported in ADO.NET (it would be a specialization of DataReader in the model, sort of).

Most people programmed ADO MD (ADO for multidimensional) instead. It encapsulated the OLE DB for OLAP Dataset in a class called Cellset. AND there is a recent release that supports .NET, called ADOMD for .NET. Cool.

After going through Resultset, Recordset, Rowset, DataReader and others, there's just a limit to how many new names you can come up with for “the object that encapsulates columns and rows”. Even when they are cells. Terminology reuse is inevitable, but confusing.

Glad we got THAT out of the way.

After writing DevelopMentor's Essential OLE DB class and teaching it to provider writers (and detail-oriented consumer writers) for a few years, I always thought that a good way to get myself acquainted with the new data model was to write a new database client provider/driver/etc whenever the model changed. I wrote a simple .NET data provider during .NET alpha 1 and published an article in MSDN magazine about it in Dec 2001. Well, the model changed again.

ADO.NET 2.0 enhanced the data provider model in some pretty revolutionary ways. It added something that was always needed, a factory for providers. This class (System.Data.Common.ProviderFactories) gets a list of providers or a provider-specific factory class (e.g. SqlClientFactory). The provider-specific factory can be used to get provider-specific Connection, Command, etc, instances. Providers that want to participate now have a special place in the config file. The 4 Microsoft providers are listed in machine.config.

In addition to this change, the new provider model is based on base classes rather than interfaces (although interfaces are still there for backward compatibility). Provider writers implement classes that derive from DbConnection, DbCommand, etc. This allows adding new functionality without breaking backward compatibility.

So I've updated the provider and posted the update on my website. The updated provider uses base classes and also has a Provider Factory class. There's also a sample program that uses it. I didn't put the provider in machine.config, but have an app.config that adds it.

An overview of the new model is provided in this MSDN online article, and two more are in the works. Look for them in MSDN online.

Enjoy.

If you've recently installed the SQL Express version of SQL Server 2005, you may or may not have noticed SQL Native Client (SNAC). If you want to use the new features of SQL Server 2005 like multiple active resultsets or snapshot isolation from OLE DB, ADO, or ODBC, you're going to need SNAC.

SNAC is meant to separate SQL Server's OLE DB provider and ODBC driver from MDAC. Currently the SQL Server OLE DB provider (SQLOLEDB) and ODBC driver ship as part of MDAC (Microsoft Data Access). A new version of Microsoft Data Access (MDAC 9.0) was planned in order to support the new SQL Server 2005 functionality. MDAC 9.0 won't happen. Instead, the current components of MDAC (2.8 SP1?) will ship as part of the Windows family of operating systems. MDAC will change infrequently, changes will not be tied to new SQL Server functionality. SNAC will be versioned with new versions of SQL Server and ship separately.

Once you've installed SNAC, you should see a new OLE DB provider (SQL Native Client) and a new ODBC driver (SQL Native Client). You need to use them instead of the older versions to get the SQL Server 2005-specific functionality. Note that they need to be coded into the connection string (OLE DB, ADO, or ODBC) or ODBC DSN. The SQLNCLI provider does not support XML like the SQLOLEDB provider does.

If you're using ADO.NET's SqlClient data provider, it has been rearchitected so it doesn't use MDAC. Or SNAC, as far as I know. If you've used SqlClient with SQL Server 2005 beta 1, you won't need "USE MDAC9" in connection strings as MDAC 9.0 isn't shipping with .NET 2.0 beta 1/SQL Server 2005 beta 2. SNAC is provided for vendors that still use OLE DB, ADO, and ODBC in the products. Nice feature. I have a soft spot in my heart for OLE DB (having taught provider writers for a few year), and having MDAC as a part of the OS is overdue. Not shipping MDAC for SQL Server changes will be a good thing.

I was checking some of the SQL Server 2005 features on the way over on the plane. The latest build that I have is SQL Express build, fairly recent. Many of the XQuery features have been updated to Nov 2003 XQuery spec. Very cool. But comments wasn't one of them. A nit, I know.

The {-- a comment --} style comments still work when using SQL Server XQuery but the new style, smiley-face comments (: a comment :) don't. They generate a syntax error.

Now I *liked* the old style comments (don't like to see my code grin at me, call me sensitive), but the smiley-face ones are part of the spec revision, unless I'm missing something. And they are supported in the client-side System.Data.SqlXml's XQueryCommand dialect of XQuery. That's even more puzzling because the two dialects (SQL Server and System.Data.SqlXml) are synchronized in other ways. For example, System.Data.SqlXml no longer supports the XQuery “LET” verb and has compile-time strong type checking like SQL Server.

I lag in reporting things I find, because I know beta 2 is still a work in progress. Thought it would be added. All of the book examples have the smiley face comments, just do “find and replace” if you run 'em. Beta 2 still isn't out yet, maybe... :)

The first blog entry got me thinking. For someone supposedly cloistered writing a book, I have been doing some heavy traveling. For the last year, I've been teaching Yukon beta 1 quite a bit. Many students were participants in the Ascend program phase 1. They are folks from software companies who write application and system software that runs on SQL Server. They came to the class, Yukon for Developers, to see what all the buzz was about, not only with T-SQL, but with revolutionary features like .NET procedural code support, user-defined types, XML and XQuery, service broker, and others. They were joined by support people and folks from MCS (Microsoft Consulting Services).

It was great fun. I met and hung out with the Ascend guys: Andy Gammuto, Don Petersen, and Roger Doherty. I actually haven't met Roger yet, he's just a voice on the phone so far. We took the show to such faraway places as Sydney and Munich; Niels did a gig in Nice.

The best part was seeing what folks thought of the new features. I've always met some people using XML in databases on real-world problems in phase 1. They enjoyed trying out the XQuery support. Phase 2 promises to be just as exciting.

So I guess that's not exactly hiding. More like “where in the world is Bob”. I've been around the block a few times. Might even qualify for 1k airplane miles this year. But that's another story.

I've finally decided to take the plunge and start this up. I've been in hiding for the last year and three quarters or so, working on a SQL Server 2005 (was Yukon) course and a companion book. The book, entitled “A First Look at SQL Server 2005 for Developers” shipped on Monday. I have a single advance copy. Amazon doesn't think it's shipped yet though, has the book's old name (with Yukon in it) and is missing an author (oh well).

Dan Sullivan and Niels Berglund are my co-authors. The book has been a long time in coming. The SQL Server folks were nice enough to let us release it (the material is based on beta 2) even though Beta 2 isn't out yet. Modulo SQL Express, which was released yesterday on MSDN. So I have a (very) few spare cycles.

I'm going to write about adventures in Ascend program phase 2, and any technical insights and info I pick up along the way. I may not be writing much right away though, I'm on my way to Tokyo for a teaching gig tomorrow morning.