[thelist] callingm sql stored proc using asp

[Ken Schaefer]

Use

SET NOCOUNT ON

in your sproc before you do any inserts/updates etc that affect your #temp 
table. This will supress the xx records affected messages which are creating 
empty recordsets.

Cheers
Ken

----- Original Message ----- 
From: "Brian Delaney" <brian.delaney at mccmh.net>
Subject: Re: [thelist] callingm sql stored proc using asp


: Thanks for the help..when I run this stored proc in query analyzer it
: does return 6 rows.
:
: I suspect that I am violating some rule that when you call a SP from an
: asp web page it cannot
: create temp tables on the fly nor call a use defined function????
:
: thanks
:
: Vlad Alexander (XStandard) wrote:
:
: >Hi Brian,
: >
: >You are returning an emtpy recordset. Your test does not catch this. In
: >order to test for empty recordsets, do this:
: >
: >If Not (rsJailDiv.BOF And rsJailDiv.EOF) Then
: >
: >End If
: >
: >By the way, it's best not to call your stored procedures with "sp_" 
prefix".
: >This prefix is reserved. "sp" does not stand for stored procedure - it
: >stands for "special". My preference is to do the following:
: >
: >getXXXX
: >updXXXX
: >delXXXX
: >
: >Regards,
: >-Vlad
: >http://xstandard.com
: >
: >
: >----- Original Message ----- 
: >From: "Brian Delaney" <brian.delaney at mccmh.net>
: >To: <thelist at lists.evolt.org>
: >Sent: Tuesday, October 12, 2004 3:52 PM
: >Subject: [thelist] callingm sql stored proc using asp
: >
: >
: >
: >
: >>I am calling a stored proc on my sql server from asp.
: >>
: >>For some reason if I call this sp sp_returnwhosnew I get the error
: >>"that this operation not allowed when the object is closed"
: >>
: >>here is the asp:
: >>
: >><%
: >>
: >>Call OpenJailDivConn()
: >>
: >>sDate = FormatDateTime(now, 2)
: >>
: >>Set rsJailDiv = Server.CreateObject("ADODB.Recordset")
: >>strSQL = "sp_returnwhosnewtest "
: >>rsJailDiv.Open strSQL, JailDivConn
: >>iCtr = 0
: >>DIM varlname
: >>DIM var
: >>If NOT rsJailDiv.EOF Then
: >>    while not rsJailDiv.EOF
: >>etc. etc.
: >>
: >>If I changed the strSQL to any other sp it works fine.
: >>
: >>The SP uses a ##TEMP table and also calls a user defined function that
: >>does date manipulation. Is this the problem?
: >>
: >>Any help will be appreciated.