Classic ASP, getting results from a SQL Server Stored Procedure

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
michealoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kblack15217Commented:
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
ap_sajithCommented:
Try
set objRS=oConn.Execute("sp_rssfeed")

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

Cheers!!
0
michealoAuthor Commented:
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

sybeCommented:
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
michealoAuthor Commented:
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
sybeCommented:
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
michealoAuthor Commented:
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
ap_sajithCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hemanth_mca123Commented:
check it up GetString function in www.4guysfromrolla.com
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.