ADODB Command Error when using adExecuteStream

[Sam]

i have ADO2.6 installed on my machine.
i am running the code on my NT workstation
with IIS,IE6.0

getting following error when i try to execute.
adoCmd.Execute , , adExecuteStream '1024

Object or provider is not capable of performing requested operation

Following is the code for this example

<%

Dim adoCmd 'As ADODB.Command
Dim adoConn ' As ADODB.Connection
Dim adoStreamQuery ' As ADODB.Stream
Dim outStrm ' As ADODB.Stream
Dim txtResults ' String for results
dim sConn ' String for connection
dim CmdStream ' as ADODB.Stream

'sConn = "Provider=SQLOLEDB;Data Source=.;Initial
Catalog=Northwind;uid=sa;pwd="
sConn = "Provider=SQLOLEDB;Data Source=GOTFONTA6\KLR;Initial
Catalog=Northwind;User ID=SA;Password=milazoqe;"

Set adoConn = CreateObject("ADODB.Connection")
Set adoStreamQuery = CreateObject("ADODB.Stream")

adoConn.ConnectionString = sConn
adoConn.Open

Set adoCmd = CreateObject("ADODB.Command")
set adoCmd.ActiveConnection = adoConn

adoConn.CursorLocation = adUseClient

Set adoCmd.ActiveConnection = adoConn
sQuery = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>"
sQuery = sQuery & "<sql:query>SELECT * FROM PRODUCTS FOR XML
AUTO</sql:query>"
sQuery = sQuery & "</ROOT>"

adoStreamQuery.Open ' Open the
command stream so it may be written to
adoStreamQuery.WriteText sQuery, adWriteChar ' Set the input
command stream's text with the query string
adoStreamQuery.Position = 0 ' Reset the
position in the stream, otherwise it will be at EOS

Set adoCmd.CommandStream = adoStreamQuery ' Set the
command object's command to the input stream set above
adoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}" ' Set
the dialect for the command stream to be a SQL query.
Set outStrm = CreateObject("ADODB.Stream") ' Create the
output stream
outStrm.Open
adoCmd.Properties("Output Stream") = response ' Set command's
output stream to the output stream just opened
adoCmd.Execute , , 1024 ' Execute the command, thus
filling up the output stream.

%>

any help is greatly appreciated.
Thanks in advance
sam

[Bulent Elmaci [MS]]

Hi,

Probably your problem is because of the fact that you are using SQL dialect
although your query is actually a template. You need to set the dialect to
template dialect below in your code:

{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}" '
Transact-SQL query
{5D531CB2-E6Ed-11D2-B252-00C04F681B71} 'XML
template query (you need to use this dialect)
{EC2A4293-E898-11D2-B1B7-00C04F680C56} 'XPath
query

Thanks,

This posting is provided "AS IS" with no warranties, and confers no rights.

~Bulent

--------------------
| From: sam...@hotmail.com (Sam)
| Newsgroups: microsoft.public.sqlserver.xml
| Subject: ADODB Command Error when using adExecuteStream
| Date: 14 Mar 2002 12:09:03 -0800

[Harish]

Thanks for the response.
But i need to know how can i get output in XML with just specifying "Select
...." : the select query and not

"<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>SELECT *
FROM PRODUCTS FOR XML
AUTO</sql:query></ROOT>"

THANKS.

[Bulent Elmaci [MS]]

Hi,

You can just set sQuery to your original query (select * from Products for
xml auto) if you use SQL dialect ({C8B522D7-5CF3-11CE-ADE5-00AA0044773D}).
By the way, I don't know if it's just a typo or not but there is a bug in
the code originally posted with the question:

Set outStrm = CreateObject("ADODB.Stream")

outStrm.Open
adoCmd.Properties("Output Stream") = outStrm ' <---
This was "response" in the original code

Thanks,

This posting is provided "AS IS" with no warranties, and confers no rights.

~Bulent

--------------------
| From: "Harish" <flyI...@indiatimes.com>
| References: <d4d8835a.02031...@posting.google.com>
<0JtAbc5yBHA.1988@cpmsftngxa07>
| Subject: Re: ADODB Command Error when using adExecuteStream
| Date: Mon, 18 Mar 2002 12:35:33 -0800