Command Streams

ADO has always supported command input in string format specified by the CommandText property. As an alternative, with ADO 2.7 or later, you can also use a stream of information for command input by assigning the stream to the CommandStream property. You can assign an ADO Stream object, or any object that supports the COM IStream interface.

The content of the command stream is simply passed from ADO to your provider, so your provider must support command input by stream for this feature to work. For example, SQL Server 2000 supports queries in the form of XML templates or OpenXML extensions to Transact-SQL. For more information about stream command support in SQL Server, see ADO Support for SQL Server XML Features in the SQL Server Books Online.

Because the details of the stream must be interpreted by the provider, you must specify the command dialect by setting the Dialect property. The value of Dialect is a string containing a GUID, which is defined by your provider. For information about valid values for Dialect supported by your provider, see your provider documentation. For information about dialects supported by SQL Server 2000, see Using Streams for Command Input in the SQL Server Books Online.

XML Template Query Example

The following example is written in VBScript to the SQL Server 2000 Northwind database.

First, initialize and open the Stream object that will be used to contain the query stream:

Dim adoStreamQuery
Set adoStreamQuery = Server.CreateObject("ADODB.Stream")
adoStreamQuery.Open

The content of the query stream will be an XML template query.

The template query requires a reference to the XML namespace identified by the sql: prefix of the <sql:query> tag. A SQL SELECT statement is included as the content of the XML template and assigned to a string variable as follows:

sQuery = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
<sql:query> SELECT * FROM PRODUCTS ORDER BY PRODUCTNAME </sql:query>
</ROOT>"

Next, write the string to the stream:

adoStreamQuery.WriteText sQuery, adWriteChar
adoStreamQuery.Position = 0

Assign adoStreamQuery to the CommandStream property of an ADO Command object:

Dim adoCmd
Set adoCmd  = Server.CreateObject("ADODB.Command"")
adoCmd.CommandStream = adoStreamQuery

Specify the command language Dialect, which indicates how the SQL Server OLE DB Provider should interpret the command stream. The dialect specified by a provider-specific GUID:

adoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"

Finally, execute the query and return the results to a Recordset object:

Set objRS = adoCmd.Execute