Solved

Classic ASP, getting results from a SQL Server Stored Procedure

Posted on 2003-10-28
10
615 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
[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
  • 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
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 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
 
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

Industry Leaders: 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!

Question has a verified solution.

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

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…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

738 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