[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Problem using an ADO.DB Stream with msxml4

Posted on 2004-04-08
6
Medium Priority
?
313 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Accepted Solution

by:
rdcpro earned 1500 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

649 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