Solved

Problem using an ADO.DB Stream with msxml4

Posted on 2004-04-08
6
294 Views
Last Modified: 2013-11-19
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
Comment
Question by:blindbull
  • 2
  • 2
  • 2
6 Comments
 
LVL 12

Expert Comment

by:dfiala13
ID: 10782452
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
 

Author Comment

by:blindbull
ID: 10782691
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
 
LVL 12

Expert Comment

by:dfiala13
ID: 10782756
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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 26

Accepted Solution

by:
rdcpro earned 500 total points
ID: 10785567
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
 
LVL 26

Expert Comment

by:rdcpro
ID: 10785583
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
 

Author Comment

by:blindbull
ID: 10831420
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article covers the basics of the Sass, which is a CSS extension language. You will learn about variables, mixins, and nesting.
Introduction Knockoutjs (Knockout) is a JavaScript framework (Model View ViewModel or MVVM framework).   The main ideology behind Knockout is to control from JavaScript how a page looks whilst creating an engaging user experience in the least …
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now