ADO Version 2.6

Team-Fly

---

ADO 2.6 has expanded its functionality to deepen its support of XML. Most of these changes have been made to the Command object to better support SQL Server's capability to pass back XML-formatted result sets. A summary of how these changes are expected to work is outlined here:

• Command Stream . With ADO 2.6 and the SQL Server 2000 version of the SQL Server OLE DB Provider, the user can retrieve the results of a command as an XML stream or execute an XML or Xpath command. This is to provide better integration with queries that return XML instead of "ordinary" Recordsets.

• Support for Singleton Selects . The Record object can now be used to retrieve the results of a singleton SELECT (a SELECT that returns a single row). This technique should result in up to a 15 percent performance improvement when executing singleton SELECTs passed to a Recordset object.

• Named Parameter Support . This means that when you write procedure calls, you'll have to specify parameters by name in many cases. This also means that when executing stored procedures, you no longer have to specify parameters positionally because these parameters can also be specified by name.

• Error Status . You can use the ADO 2.6 Field.Status property to retrieve more information about an error. In addition, ADO 2.6's overall error handling has been improved, adding far more information about errors than ever before provided.

• User/Group Properties. Using ADOX 2.6, the user can access properties of the User and Group objects. When using the Jet OLE DB Provider, the user will now be able to set the PIN for a user or group via the Jet OLEDB:Trustee PIN property.

• Expose New Server Features . ADO 2.6 adds support for SQL Server multi-instances, big-Int and sql_variant datatypes, User-Defined Functions (UDF), encryption, and column-level collation.

• Performance Improvements . ADO 2.6 supports new escape sequences for remote procedure calls using the new ODBC escape sequence, which is a faster alternative to the CALL escape sequence. ADO 2.6 also makes use of the internal stored procedure sp_prepexec for ODBC and OLE DB. This system-stored procedure reduces the metadata sent by server to client, resulting in a reduction of round trips to server (for prepare and unprepare).

SQL XML

A number of ADO 2.6 changes are planned to support SQL Server 2000's capability to return XML. These changes include:

• Support for the new FOR XML.TSQL clause . FOR XML is an extension to SQL Server' s TSQL SELECT statement. It tells SQL Server to return the query results as an XML document fragment using one of three modes—Raw, Auto, or Explicit—to format the XML.

• URL Access to SQL Server. Using an ISAPI filter hosted by IIS, ADO 2.6 provides access to SQL Server 2000 data by simply pointing to a virtual root. We discussed this earlier in the XML integration section of this chapter. This new ISAPI filter supports direct execution of an SQL statement passed in the URL, through an XML template or what's called an Xpath template.

• Annotated Schemas. If you need a look at your database schema, ADO 2.6 supports an XML view of your relational database. A schema authored to the XML-DATA Reduced specification is annotated (additional elements and attributes are added to it) with the mappings between the elements and attributes declared within the schema and the columns of tables or views in the database.

• OpenXML. This is a new extension to the SQL language syntax providing a rowset interface to XML data. It can be used within TSQL stored procedures to build XML documents from data queried from the database.

ODBC Development

All ODBC work has come to a halt—the ODBC and Jet providers have been placed in "QFE" mode. This means no further changes will be made to this technology, and only serious bugs will be fixed.

---

Team-Fly