Temporary tables breaking the recordset?

Temporary tables breaking the recordset?

Post by Eric S » Sun, 06 Feb 2000 04:00:00

Been beating my head against this one for some time, I'm hoping some of
you guys will have some insight into what's going on.

I have an ASP application that can run one of any number of SQL Server
7.0 stored procedures.  The application passes two dates to the
procedure and loops through the resulting recordset.  This has worked
flawlessly with a number of these queries.  The latest one, though,
bombs out.

I've debugged my ASP code until I'm confident there's nothing wrong
with it, and I've stripped it down to the bare essentials.  What I need
to know from you SQL gurus is if there is something fundamentally
different about the results that are returned from a stored procedure
that includes temporary tables (the only real difference between this
SP and the others).  If you want, I can post the code, but it's big and
ugly (certainly could be optimized).  In essence, here's what it does:

-- drops the 3 temporary tables from the previous execution of the SP.
-- re-creates the temporary tables.
-- pulls data from a table and inserts it into the temporary tables.
-- INNER JOINs the temporary tables, SELECTs the data, and displays it.
-- Query Analyzer performs this flawlessly and displays the data.

My ASP page, however, tells me "The operation requested by the
application is not allowed if the object is closed."  Ordinarily I
would suspect the code, but the simple fact is that the object is NOT
being closed in the ASP code.  Is it possible that it's getting closed
before it gets to the page?

I even tried creating a permanent table in the DB, selecting the data
from the temporary tables into that table, then selecting * from that
table into the recordset, but I get the same error.

How should I handle this differently?  My apologies for the long post.
Again, let me know if you want to see the query.

Thanks very much in advance.

Eric S.

Sent via Deja.com http://www.deja.com/
Before you buy.


Temporary tables breaking the recordset?

Post by Eric S » Sun, 06 Feb 2000 04:00:00

FYI, Purvi Shah helped me with this one by suggesting I add "SET
NOCOUNT ON" to the beginning of the stored procedure.  Worked like a
champ -- the "(x row(s) affected)" lines had been getting in the way.

Eric S.

Sent via Deja.com http://www.deja.com/
Before you buy.


1. How to Return Multiple Tables/Recordsets Using Temporary Table

SP return multiple tables using

-- Here is Sp result recordset
Select * From
-- Here is other sp recordset throw temporary session-life table

However VB ADO Connection reports that #Temp1 is invalid object. Both recordsets are quered using the same Connection object. Sample looks like

    Set Cmd.ActiveConnection = objConnection
    Cmd.CommandText = "SpName"
    Cmd.CommandType = adCmdStoredProc
    Set rs_sp = Cmd.Execute ' works fine!

    ' Next Line breaks with error #Temp1 is invalid object
    Rs.Open "select * from #Temp1", objConnection

Whats wrong?

Thanks in advance
Vladimir N. Goida


3. Temporary Database with temporary Recordset/Resultset

4. Error when starting pmon on Oracle for linux

5. Partial Recordset Retrieval via Temporary Table

6. Mirroing MySQL database

7. recordset data -> temporary table

8. addnew and autonumber behaviour

9. recordset data -> temporary table (sql-server)

10. recordset data -> temporary table

11. Disconnected recordsets & SQL temporary tables

12. ADO Recordset is empty when results are returned from temporary table

13. How temporary are temporary tables?