PROBLEMS with results of a stored procedure thats using a temp table

[questhaven]

Hi there - I have a question I am using the code below to try and get the results returned by a stored procedure that contains a temporary table. When I try and run the code below it gives me an "Operation is not allowed when the object is closed." error. Does this have something to do with the fact that I am using a temp table in my stored procedure?

Any help is greatly appreciated!

Here is the code I am using to call the Stored Procedure:

sql = "exec sql_testSP '2/28/2003', '3/31/2003', 3"

Response.Write(sql)
set rs = conn.execute(sql)

if not rs.eof then
do while not rs.eof
Response.Write(rs(&quot;user_id&quot & &quot;-&quot; & rs(&quot;CTLDate&quot & &quot;-&quot; & rs(&quot;amt&quot & &quot;<BR>&quot
rs.movenext
loop
end if

AND here is the stored procedure:

CREATE PROCEDURE sql_testSP
@datebegin datetime,
@dateend datetime,
@User_ID [int]

AS

--declare a dates table
create table #dates(User_ID int, CtlDate smalldatetime)
Declare @dt datetime
Set @dt=@datebegin --'2003-03-01'
Set @user_id=3

--Load the dates table with all the days in March 2003
--Note that the UserID is also loaded into the table
While @dt<= @dateend --'2003-03-31'
Begin
Insert #dates values(@User_ID, @dt)
--increment the date
Set @dt=@dt+1
End

--Select the billing records for the specified UserID
Select
d.User_ID,
d.CtlDate,
--If the current date has a billing record choose its Amt
--otherwise choose the Amt from the most recent record
Amt=coalesce(t.SpaceUsed,
(Select top 1 spaceUsed From transactions
Where User_ID=d.User_ID And BillingDate<d.CtlDate
Order By BillingDate Desc))
From #dates d
--Use a LEFT JOIN becauase there will not be a row
--to match each date in the dates table
Left Join transactions t
On d.User_ID=t.User_ID
And d.CtlDate=t.BillingDate
GO

 

[hithere]

haven't tried an sp with a temp table but...I don't see in your code where you open a conn to the db. is the conn object required? I would think so.
hth
mb
&quot;Where's the Ka-Boom? There's supposed to be an Earth-shattering Ka-Boom!&quot;
Marvin the Martian

 

[MarkSweetland]

Put a &quot;set nocount on&quot; at the start of your sp. Temp table operations output a &quot;xx rows affected&quot; message that the connection object may interpret as a recordset response...

Mark