Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 619
  • Last Modified:

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
0
michealo
Asked:
michealo
  • 3
  • 3
  • 2
  • +2
1 Solution
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
hemanth_mca123Commented:
check it up GetString function in www.4guysfromrolla.com
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now