Can someone give me an example of how to use a
stored procedure that returns XML data?
I want to call the SP from a VBScript program,
and put the XML into a data island in the generated HTML.
12  2885 
Peter Morris wrote: Can someone give me an example of how to use a
stored procedure that returns XML data?
I want to call the SP from a VBScript program,
and put the XML into a data island in the generated HTML.
Do you mean a procedure that runs a query containing a FOR XML clause?
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl... Peter Morris wrote: Can someone give me an example of how to use a
stored procedure that returns XML data?
I want to call the SP from a VBScript program,
and put the XML into a data island in the generated HTML.
Do you mean a procedure that runs a query containing a FOR XML clause?
Bob Barrows
Yes, my SP has FOR XML AUTO, ELEMENTS. It returns the results
as XML.
Now, I have a VBscript program that dynamically generates HTML
output. I want to run my SP from VBScript, and insert the XML as
a data island in my HTML.
Peter Morris wrote: "Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl... Peter Morris wrote: Can someone give me an example of how to use a
stored procedure that returns XML data?
I want to call the SP from a VBScript program,
and put the XML into a data island in the generated HTML.
Do you mean a procedure that runs a query containing a FOR XML
clause? Bob Barrows
Yes, my SP has FOR XML AUTO, ELEMENTS. It returns the results
as XML.
Now, I have a VBscript program that dynamically generates HTML
output. I want to run my SP from VBScript, and insert the XML as
a data island in my HTML.
I have some code somewhere that uses a Stream to get te XML. Let me look for
it: ... ah! Here it is:
<%@ Language=VBScript %>
<%
option explicit
dim cn, rs,sQuery, cmd, xmldoc
Const adExecuteStream = &H00000400
Const adCmdText = &H0001
set cn=server.CreateObject("adodb.connection")
cn.open "provider=sqloledb;data source=????;" & _
"user id = ???; password=????;initial catalog=northwind"
Set xmldoc=server.createobject("msxml2.domdocument")
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
sQuery = "SELECT * FROM PRODUCTS ORDER BY PRODUCTNAME FOR XML auto"
cmd.CommandText = sQuery
cmd.Properties("xml root").Value = "root"
cmd.Properties("Output Stream") = xmldoc
cmd.Execute , , adExecuteStream + adCmdText
set cmd=nothing
cn.close
set cn=nothing
%>
<html>
....
<XML id="xmlData">
<%=xmldoc.xml%>
</XML>
</html>
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Hello Bob,
I have posted my following issue in the sqlserver newsgroup. I have been
using similar code for about 3 years on and IIS/W2K platform. It has
performed flawlessly.
We bought a new IIS6/W2K3 box and the same code hangs at this line of code
(borrowed from your sample):
cmd.Execute , , adExecuteStream + adCmdText
We can go days without a problem and then all of a sudden whamo!!! I ran
iisstate against one of the w3_wp.exe and it appears to be hanging when
loading
mlang.dll. Any help that you can lend me would be greatly appreciated.
Thanks in advance.
"Bob Barrows [MVP]" wrote: Peter Morris wrote: "Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl... Peter Morris wrote:
Can someone give me an example of how to use a
stored procedure that returns XML data?
I want to call the SP from a VBScript program,
and put the XML into a data island in the generated HTML.
Do you mean a procedure that runs a query containing a FOR XML
clause? Bob Barrows
Yes, my SP has FOR XML AUTO, ELEMENTS. It returns the results
as XML.
Now, I have a VBscript program that dynamically generates HTML
output. I want to run my SP from VBScript, and insert the XML as
a data island in my HTML.
I have some code somewhere that uses a Stream to get te XML. Let me look for
it: ... ah! Here it is:
<%@ Language=VBScript %>
<%
option explicit
dim cn, rs,sQuery, cmd, xmldoc
Const adExecuteStream = &H00000400
Const adCmdText = &H0001
set cn=server.CreateObject("adodb.connection")
cn.open "provider=sqloledb;data source=????;" & _
"user id = ???; password=????;initial catalog=northwind"
Set xmldoc=server.createobject("msxml2.domdocument")
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
sQuery = "SELECT * FROM PRODUCTS ORDER BY PRODUCTNAME FOR XML auto"
cmd.CommandText = sQuery
cmd.Properties("xml root").Value = "root"
cmd.Properties("Output Stream") = xmldoc
cmd.Execute , , adExecuteStream + adCmdText
set cmd=nothing
cn.close
set cn=nothing
%>
<html>
....
<XML id="xmlData">
<%=xmldoc.xml%>
</XML>
</html>
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
ajsmith02 wrote: Hello Bob,
I have posted my following issue in the sqlserver newsgroup. I have
been using similar code for about 3 years on and IIS/W2K platform.
It has performed flawlessly.
We bought a new IIS6/W2K3 box and the same code hangs at this line of
code (borrowed from your sample):
cmd.Execute , , adExecuteStream + adCmdText
We can go days without a problem and then all of a sudden whamo!!! I
ran iisstate against one of the w3_wp.exe and it appears to be
hanging when loading
mlang.dll. Any help that you can lend me would be greatly
appreciated.
I wish I could help, but my company has not moved to that platform as yet.
If you cannot find anything in the KB, then i suggest you open a case with
MS Product Support. If it turns out to be a bug, you won't be charged for
the call.
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:eV**************@TK2MSFTNGP09.phx.gbl...
I have some code somewhere that uses a Stream to get te XML. Let me look
for it: ... ah! Here it is:
<%@ Language=VBScript %>
<%
option explicit
dim cn, rs,sQuery, cmd, xmldoc
Const adExecuteStream = &H00000400
Const adCmdText = &H0001
set cn=server.CreateObject("adodb.connection")
cn.open "provider=sqloledb;data source=????;" & _
"user id = ???; password=????;initial catalog=northwind"
Set xmldoc=server.createobject("msxml2.domdocument")
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
sQuery = "SELECT * FROM PRODUCTS ORDER BY PRODUCTNAME FOR XML auto"
cmd.CommandText = sQuery
cmd.Properties("xml root").Value = "root"
cmd.Properties("Output Stream") = xmldoc
cmd.Execute , , adExecuteStream + adCmdText
set cmd=nothing
cn.close
set cn=nothing
%>
<html>
...
<XML id="xmlData">
<%=xmldoc.xml%>
</XML>
</html>
HTH,
I've tried running your code, Bob. It returns an error message at line 15
and if I comment that out, another at line16
That's these lines. cmd.Properties("xml root").Value = "root"
cmd.Properties("Output Stream") = xmldoc
Error message is:
ADODB.Command (0x800A0CC1)
Item cannot be found in the collection corresponding to the
requested name or ordinal.
"Peter Morris" <nospam.ple@se> wrote in message
news:b%****************@text.news.blueyonder.co.uk ...
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:eV**************@TK2MSFTNGP09.phx.gbl...
I have some code somewhere that uses a Stream to get te XML. Let me look
for it: ... ah! Here it is:
<%@ Language=VBScript %>
<%
option explicit
dim cn, rs,sQuery, cmd, xmldoc
Const adExecuteStream = &H00000400
Const adCmdText = &H0001
set cn=server.CreateObject("adodb.connection")
cn.open "provider=sqloledb;data source=????;" & _
"user id = ???; password=????;initial catalog=northwind"
Set xmldoc=server.createobject("msxml2.domdocument")
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
sQuery = "SELECT * FROM PRODUCTS ORDER BY PRODUCTNAME FOR XML auto"
cmd.CommandText = sQuery
cmd.Properties("xml root").Value = "root"
cmd.Properties("Output Stream") = xmldoc
cmd.Execute , , adExecuteStream + adCmdText
set cmd=nothing
cn.close
set cn=nothing
%>
<html>
...
<XML id="xmlData">
<%=xmldoc.xml%>
</XML>
</html>
HTH,
I've tried running your code, Bob. It returns an error message at line 15
and if I comment that out, another at line16
That's these lines. cmd.Properties("xml root").Value = "root"
cmd.Properties("Output Stream") = xmldoc
Error message is:
ADODB.Command (0x800A0CC1)
Item cannot be found in the collection corresponding to the
requested name or ordinal.
I don't fully understand how the code works. Can you please tell
me what's wrong?
Peter Morris wrote: "Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:eV**************@TK2MSFTNGP09.phx.gbl...
I've tried running your code, Bob. It returns an error message at
line 15 and if I comment that out, another at line16
That's these lines. cmd.Properties("xml root").Value = "root"
cmd.Properties("Output Stream") = xmldoc
Error message is:
ADODB.Command (0x800A0CC1)
Item cannot be found in the collection corresponding to the
requested name or ordinal.
This is most likely due to a problem with the version of ADO that is running
on your server. You should get and install the latest version of MDAC from
the MS website.
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Peter Morris wrote:
I don't fully understand how the code works. Can you please tell
me what's wrong?
I've already replied to your previous message. You waited only 3 min. before
asking again?? I know it must seem like it sometimes, but I don't live in
these newgroups :-)
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:eU**************@TK2MSFTNGP11.phx.gbl... Peter Morris wrote:
I don't fully understand how the code works. Can you please tell
me what's wrong?
I've already replied to your previous message. You waited only 3 min.
before asking again?? I know it must seem like it sometimes, but I don't
live in these newgroups :-)
On review, I thought my original post might appear a bit abrupt and
rude to someone offering assistence. I thought the second post would
make it more polite.
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2******************@TK2MSFTNGP11.phx.gbl... Peter Morris wrote: "Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:eV**************@TK2MSFTNGP09.phx.gbl...
I've tried running your code, Bob. It returns an error message at
line 15 and if I comment that out, another at line16
That's these lines. cmd.Properties("xml root").Value = "root"
cmd.Properties("Output Stream") = xmldoc
Error message is:
ADODB.Command (0x800A0CC1)
Item cannot be found in the collection corresponding to the
requested name or ordinal.
This is most likely due to a problem with the version of ADO that is
running on your server. You should get and install the latest version of
MDAC from the MS website.
Bob Barrows
Thank you, I'll try that.
Peter Morris wrote: "Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:eU**************@TK2MSFTNGP11.phx.gbl... Peter Morris wrote:
I don't fully understand how the code works. Can you please tell
me what's wrong?
I've already replied to your previous message. You waited only 3 min.
before asking again?? I know it must seem like it sometimes, but I
don't live in these newgroups :-)
On review, I thought my original post might appear a bit abrupt and
rude to someone offering assistence. I thought the second post would
make it more polite.
Oh, I see. Well, that's the problem with email and newsgroup posts. No body
language or voice inflection to help us judge the content. What seemed more
polite to you as you wrote and sent it, seemed like a rude "what's keeping
you?" to me. Something like this would have helped:
Oh wait! That seemed rude and abrupt. Let me try again: ....
Now there's no room for misinterpretation.
I would have replied:
There was no need to worry. It did not seem rude to me at all. In fact, we
appreciate succinctness since it allows us to process more posts in a given
period of time.
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM" This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: mjm |
last post by:
Folks,
Stroustrup indicates that returning by value can be faster than
returning by reference but gives no details as to the size of the
returned object up to which this holds.
My question is...
|
by: Derek |
last post by:
Some authors advocate returning const objects:
const Point operator+(const Point&, const Point&);
^^^^^
Returning a const object prevents some bad code from compiling:
Point a, b, c;
(a +...
|
by: Fraser Ross |
last post by:
I need to know the syntax for writing a reference of an array. I haven't
seen it done often. I have a class with a member array and I want a member
function to return an reference to it. ...
|
by: Materialised |
last post by:
I am writing a simple function to initialise 3 variables to pesudo
random numbers.
I have a function which is as follows
int randomise( int x, int y, intz)
{
srand((unsigned)time(NULL));
x...
|
by: wonderboy |
last post by:
Hey guys,
I have a simple question. Suppose we have the following
functions:-
//-----My code starts here
char* f1(char* s)
{
char* temp="Hi";
return temp;
|
by: Randy |
last post by:
Hello,
I have a web service in which I'm doing a query to an Access database and
returning the resulting XML data when I do the return from the service...
public string AOS_Data(string sql)
{...
|
by: Faustino Dina |
last post by:
Hi,
The following code is from an article published in Informit.com at
http://www.informit.com/guides/content.asp?g=dotnet&seqNum=142. The problem
is the author says it is not a good idea to...
|
by: I.M. !Knuth |
last post by:
Hi. I'm more-or-less a C newbie. I thought I had pointers under control until
I started goofing around with this:
...
|
by: EvilOldGit |
last post by:
const Thing &operator++(int)
{
Thing temp = *this;
operator++();
return temp;
}
Is this code robust ?
I get a compiler warning about returning a reference to a a local,
which I guess is...
|
by: pauldepstein |
last post by:
Below is posted from a link for Stanford students in computer
science.
QUOTE BEGINS HERE
Because of the risk of misuse, some experts recommend never returning
a
reference from a function or...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
