We have an in-house developed data warehousing application that uses ASP and
SQL Server 2000. One portion of the code utilizes temporary tables for
intermediate data manipulation and has worked for over 4 years with no
problem (under windows server 2000).

We recently migrated to Windows Server 2003 and the persistence of the
temporary tables has gone away. Creating a table does not generate an error
however the table cannot be found by subsequent SQL commands. We first
noticed this with "local" temporary tables ("#temptab") where the table was
created by one ASP page and referenced by another page in the same session.
Extensive testing was done with the following results (all testing was as
SA):

1. Persistence is totally absent for "#" tables - even if the reference
immediately follows the create.
2. Global temporary tables appear to have persistence but only with in
the page creating the table.
3. The commands executed by the ASP pages function properly in query
analyzer.
4. The logic functions properly under Windows Server 2000.

We have had to work around this problem by using "real" tables instead of
temporary but we want to be able to use temporary tables.
Searches of Microsoft have returned nothing of any help - 1 reference to
Sharepoint but we don't have that installed.

Where do we look next?

Glenn Vaughn
Colorado Community College System - Information Technology
Pueblo Colorado

[quoted text, click to view]

This should NEVER have worked, and I have never been able to make this work
in IIS 4.0, 5.0, 5.1 or 6.0. If you want a table to be available between
ASP pages, make a permanent table. #temp tables are only valid throughout
the life of a SQL Server session, which is not the same thing as an ASP
session. A SQL Server session is created each time you establish a
connection to SQL Server... and unless you are storing an active connection
in a session object (which you should never do... see
http://www.aspfaq.com/2053), you are doing this separately on each page.

Why not have one real permanent table, and use sessionID as a key? (Some
ideas here on keeping it clean after sessions expire:
http://www.aspfaq.com/2491)

I've never seen anyone try to use a #temp table across an ASP session, and
I've never seen a valid reason to use a ##global temp table as opposed to a
#local.

--
http://www.aspfaq.com/
(Reverse address to reply.)