Temporary tables in SQL Server and ASP

what i suggest is when you create temp table and drop them it has to be all in one tread / connection. I have an example here to create / insert and drop the temp table :

set objConn = Server.CreateObject("ADODB.Connection")
set objConn = Session("myDBConnection")

SqlStr = "create table #t_temp ("
& "ManifestID numeric(9,0), "_
& "CreateDate datetime)"
objConn.Execute SqlStr

SqlStr = "Insert into #t_temp (ManifestID , CreateDate) "_
& "Values (10, '10/25/2004')"
objConn.Execute SqlStr

Set objRS = Server.CreateObject("ADODB.RecordSet")
StrSql = "SELECT * FROM #t_temp order by ManifestID"
objRS.Open StrSql, objConn, 3, 1

objConn = Nothing

Hope this will solve the problem.

I'm trying to share a temporary table between 2 Stored Procedures. The outer procedure creates the table, but the inner procedure [Execute sp_Inner ....] can't access the table.

2nd case:
From ASP code, I call a Stored Procedure that generates a temporary table, but again, the following ASP code can't access the table.

The 1st case I have a work-around [perminant tables]

For the 2nd case, I'd like to have different tables if/when different users access this page so the "perminant table" work-around is a readers/writers conflict waiting to happen.

In the 2nd case, the S.Proc and the SQL to access the table are done thru the same ADO.Session object.

Should this work, or am I doing something wrong, and if so, does anyone know how to make this work?