Solved

Classic ASP, getting results from a SQL Server Stored Procedure

Posted on 2003-10-28
10
606 Views
Last Modified: 2008-01-09
This line isn't returning any data.

The connection all works with other queries
The stored procedure runs from Query Analyzer

Response.Write oConn.Execute("sp_rssfeed").GetString()

the code for this came from http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=76

The page executes without flagging an error
0
Comment
Question by:michealo
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 4

Expert Comment

by:kblack15217
ID: 9635341
Try Response.Write oConn.Execute("exec sp_rssfeed").GetString()

Also,
make sure the user that is making the connection has execute rights on that SP object.
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9635380
Try
set objRS=oConn.Execute("sp_rssfeed")

While Not objRS.EOF
response.write objrs(0) & "<br>"
objRS.Movenext
WEnd

Cheers!!
0
 

Author Comment

by:michealo
ID: 9635526
Kblack,

Thanks for replying, however a test stored procedure executes perfectly without the need for the additional exec.

I might add that the stored procedure uses the SQL Server 2000 FOR XML EXPLICIT directive and that this may be the root of the probelem

ap_sajith,

Thanks for replying, I did try your suggestion but it gives a type mismatch error, but in any case as it works for other Stored Procedures I really want to understand why it doesn't work for this one.

The result set from sp_rssfeed is:

<rss><channel><item><title>123456</title><link>http://1234.com</link><description>sadfsdfsdafsdafasdf</description></item></channel></rss>

and is on one row again FOR XML EXPLICIT maybe mangling it somehow

Thanks
Micheal
0
 
LVL 28

Expert Comment

by:sybe
ID: 9636437
just some ideas
- i always use "exec sp_name" when calling a stored procedure, maybe it is not required, but i never even got to testing it.
- Maybe oConn.Execute("sp_rssfeed").GetString() does not return a string, so that the Response.Write has nothing to write. That happens sometimes when methods of objects are too deeply nested. In this case at first view your code should work, but it's worth trying to find out.

<%
Response.write Typename(oConn.Execute("sp_rssfeed").GetString())
%>

<%
Set oRS = oConn.Execute("exec sp_rssfeed")
sResult = oRS.GetString()
Response.wriite sResult
%>
0
 

Author Comment

by:michealo
ID: 9636494
sybe,

I tried that but it didn't make any difference, it is similar to what kblack15217 suggested, I am not convinced that puttig it in a recordset is useful considering there is only one row and one column of data returned by the stored procedure

Thanks
Micheal
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 28

Expert Comment

by:sybe
ID: 9636518
Does the stored procedure return a recordset (cursor?) or does it return a string? It seems to return some XML string, and why should it return a recordset then.

Maybe writing the code of your SP here would help.

<%
Response.Write oConn.Execute("exec sp_rssfeed")
%>
0
 

Author Comment

by:michealo
ID: 9636641
sybe,

select
1 as Tag,
Null as Parent,
Null as [rss!1!title],
Null as [channel!2!title],
Null as [item!3!title!element],
Null as [item!3!link!element],
Null as [item!3!description!element]
from tbl_rss

UNION ALL

select
2 as Tag,
1 as Parent,
Null as [rss!1!title],
Null as [channel!2!title],
Null as [item!3!title!element],
Null as [item!3!link!element],
Null as [item!3!description!element]
from tbl_rss

UNION ALL
select
3 as Tag,
2 as Parent,
Null as [rss!1!title],
Null as [channel!2!title!element],
tbl_items.title as [item!3!title!element],
tbl_items.link as [item!3!link!element],
tbl_items.itemdesc as [item!3!description!element]
from tbl_items
FOR XML EXPLICIT
0
 
LVL 21

Accepted Solution

by:
ap_sajith earned 250 total points
ID: 9641270
Try this...

Dim dbConn, oCMD, oStream, sXML

Set dbConn = Server.CreateObject("ADODB.Connection")
dbConn.Open "DSN=myDB;UID=myUser;Pwd=myPWD"

Set oCMD = Server.CreateObject("ADODB.Command")
Set oStream = Server.CreateObject("ADODB.Stream")

oStream.Open

With oCMD
.CommandText = "myProc"
.CommandType = adCmdStoredProc
.ActiveConnection = dbConn
.Properties("Output Stream") = oStream
.Execute , , adExecuteStream
End With

sXML = oStream.ReadText

Set oCMD = Nothing
Set oStream = Nothing
dbConn.Close: Set dbConn = Nothing

You nned to use the above format to retrieve the XML string.

Cheers!!
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9641283
0
 

Expert Comment

by:hemanth_mca123
ID: 9677842
check it up GetString function in www.4guysfromrolla.com
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

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

21 Experts available now in Live!

Get 1:1 Help Now