Solved

Problem using an ADO.DB Stream with msxml4

Posted on 2004-04-08
6
304 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
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

Containers and Docker for Everyone

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
Shoutout to Emily Plummer (http://www.experts-exchange.com/members/eplummer26.html) for giving me this article! She did most of it, I just finished it up and posted it for her :)    Introduction In a previous article (http://www.experts-exchang…
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…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

688 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