asked on

Problem using an ADO.DB Stream with msxml4

I have a vb com+ object that has a function that is supposed to return XML from a stored procedure.

It gets the XML via an ADO stream object.

My project references has;

MS ActiveX Data Objects 2.6 Library
MS XML v4.0

I run the VB from its test harness in the project group it gives the following error;

MSXML3: Reference to undeclared namespace prefix: 'sql'.

If I debug print the XML out it before writing it to the stream it gives ;

<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:header><sql:param name='ID'>
1
</sql:param></sql:header><sql:query>
exec sp100PaAddress_Select @ID
</sql:query></ROOT>

dfiala13

Try this...
<ROOT >
<sql:header xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
<sql:param name='ID'>1</sql:param>
</sql:header>
<sql:query xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
exec sp100PaAddress_Select @ID
</sql:query>
</ROOT>

blindbull

ASKER

Thanks for your message.

When I apply the code above I get :

MSXML3: XML document must have a top level document

The above error occurs on the line;

cmd.Execute , , adExecuteStream

I have created another function that works. It is different but it receives only the variable from the ASP and uses no XML document for the query. It only uses the stored procedure call.

I don't know if there is a difference in speed, perhaps the stream which does not pass in the XML query string is faster?

Here is the snipit of code from alternative function;

With adoCmd
.ActiveConnection = adoCnn
.CommandText = "sp100PaAddress_Select"
.CommandType = adCmdStoredProc
Set adoPrm = .CreateParameter _
("MemberID", adInteger, adParamInput, 5, memberID)
.Parameters.Append adoPrm
.Properties("Output Stream") = adoOutStr
.Execute , , adExecuteStream
End With

dfiala13

Sorry, problem was the <ROOT >
should have been <ROOT>

I tend to stick with the direct calls myself instead of sending over the Query formatted in XML. There is probably some slight speed difference as you are sending less across the wire and SQL doesn't have to translate into its native language, but it is probably not noticable for most queries.

ASKER CERTIFIED SOLUTION

rdcpro

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rdcpro

I didn't notice dfiala13's namespace on the sql:query...but in any case it still has to be on the root, so the returned XML is well-formed.

Regards,
Mike Sharp

blindbull

ASKER

It was a for xml explicit stored procedure.

I've got it working without sending the formatted query and via a direct call. I've marked Mike Sharps answer as the accepted one as it seems most likely to be the cause. Although we will never know for sure as now that I have it working I am progressing onto the next stage of my project. Might re-visit later and try some of the things suggested by Mike.