Solved

Classic ASP, getting results from a SQL Server Stored Procedure

Posted on 2003-10-28
10
614 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
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!

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How can I do a Massively massive community reviewing-and-grading site? 7 79
Error in query expression 3 54
Date on a table 16 33
VB script help 23 30
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 …
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
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…

685 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