• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

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>
0
blindbull
Asked:
blindbull
  • 2
  • 2
  • 2
1 Solution
 
dfiala13Commented:
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>
0
 
blindbullAuthor Commented:
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

0
 
dfiala13Commented:
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.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
rdcproCommented:
There should be no difference whatsoever between <ROOT > and <ROOT>.  But the SQL XML namespace must be on the root element, so that the returned XML (which is a fragment) is contained in the root element.  The namespace can't go on the header, because the header is optional, and sql:query isn't a child.

I think the problem might be that the version of SQL XML that you have doesn't support MSXML 4.  I believe it was a late service pack that added MSXML version 4.  Make sure you have the latest, and make sure you're using SQL XML 3.0, not the default version that shipped with SQL Server.  It seems odd that the error is thrown by MSXML 3, but your project reference is MSXML 4.

Does your sproc use the FOR XML clause?  It's been my experience that you get the SQL XML namespace on the returned XML, but the elements in it are not scoped to it, so you shouldn't have any sql prefixes in the returned data, unless your sproc is doing something weird.

As for the speed difference, I've never benchmarked it, but I can't believe its significant.  The bottleneck on high load conditions is usually SQL Server anyway, and the query on SQL Server uses ole db, handled via the sql xml dll.

I usually use HTTP and templates, because then my data tier is completely decoupled from the program logic, and I just use regular streams to get the XML.  But this is a little harder to deploy, I suppose, than a com object.  If deployment isn't an issue, you might consider this approach.  You can also use a POST to the virtual root, and use updategrams an such.

Regards,
Mike Sharp
0
 
rdcproCommented:
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


0
 
blindbullAuthor Commented:
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.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now