Solved

Problem using an ADO.DB Stream with msxml4

Posted on 2004-04-08
6
296 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Vb.net XML Read all nodes and 4 72
Problem to echo 6 78
Problem to ToolkitScriptManager 2 51
RSS is valid, but improvement recommendations are confusing. 3 42
Preface In the first article: A Better Website Login System (http://www.experts-exchange.com/A_2902.html) I introduced the EE Collaborative Login System and its intended purpose. In this article I will discuss some of the design consideratio…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

929 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

11 Experts available now in Live!

Get 1:1 Help Now