Data access in XML from Sql-server200 using asp

[Vaibhav]

Dear all,

i wornder is some one help me.

i want to access the the data from ms-sql-server2000 to xml file using asp so my
code is as



<%@ Language=VBScript %>

<!--#include file="adovbs.inc" -->
<%

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

Const adExecuteStream = 0

sConn = "Provider=SQLOLEDB;Data Source=TRIDT028;Initial Catalog=Northwind;uid=sa;pwd=trident"
'sConn = "Provider=SQLXMLOLEDB.3.0;Data Provider=SQLOLEDB;Data Source=TRIDT028;Initial Catalog=Northwind;uid=sa;pwd=trident"

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.Properties("Output Stream") = outStrm
adoCmd.Execute , , adExecuteStream ' Execute the command, thus filling up the output stream.
%>

but it is giving the error

ADODB.Command (0x800A0CB3)
Object or provider is not capable of performing requested operation.

if i will use the SQLXMLOLEDB as provider and OLEDB as data provider
then it is giving the error

SQLXMLOLEDB Provider command object can only execute to a stream.

so please suggest me the solution.
thanking u

Prashant

[Bob Barrows [MVP]]

Vaibhav wrote:

> but it is giving the error
>
> ADODB.Command (0x800A0CB3)
> Object or provider is not capable of performing requested operation.
>
> if i will use the SQLXMLOLEDB as provider and OLEDB as data provider
> then it is giving the error
>
> SQLXMLOLEDB Provider command object can only execute to a stream.
>
> so please suggest me the solution.
> thanking u
>

This code works fine on my machine (it uses dynamic sql for demonstrational
purposes: I have also successfully used stored procedures for this):

<%
option explicit
Response.ContentType="text/xml"
dim cn, rs,sSQL,cmd,xmldoc
'Const adExecuteStream = &H00000400
'Const adCmdText = &H0001
set cn=server.CreateObject("adodb.connection")

sSQL="SELECT * FROM Persons ORDER BY LName FOR XML AUTO "
cn.open "provider=sqloledb;data source=localhost;" & _
"user id = xxxxxxx; password=xxxxxxx;initial catalog=pubs"
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandText=sSQL
cmd.CommandType=adCmdText

dim str
Set str =server.createobject("adodb.stream")
cmd.Properties("xml root").Value = "root"
str.Open
cmd.Properties("Output Stream") = str
cmd.Execute , , 1024

set xmldoc=server.CreateObject("msxml2.domdocument")
xmldoc.loadxml str.ReadText
str.close
xmldoc.save Response
cn.close
set cn=nothing
%>

If you still get the error, then you may need to upgrade your MDAC
installation.

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"