Help with expiring temp tables in SQL Server 2000

[pds65]

This is driving me nuts. I'm out of ideas. Please help.

Here's the ASP code which accesses SQL Server 2000:

sSQL = "CREATE TABLE [" & tablename & "] ( " & _
       "[photo] [varchar] (50) NOT NULL, " & _
       "[mydate] [smalldatetime] NOT NULL " & _
       ") ON [PRIMARY]"
dbconn.Execute(sSQL)
For i = 0 to UBound(arrA) - 1
   dbconn.Execute("INSERT INTO " & tablename & " (photo,mydate) VALUES ('" & Replace(arrA(i),"'","''") & "','" & arrB(i) & "')")
Next
Set rst = dbconn.Execute("SELECT TOP 1 * FROM " & tablename & " ORDER BY mydate DESC")
sPhoto = rst.Fields("photo").Value
rst.Close

Here's an example of an insert query that WORKS:

INSERT INTO #p600 (photo,mydate) VALUES ('D10-05-0042 Marie J 04.JPG','4/29/2005')

BUT, this insert query will NOT work:

INSERT INTO #p601 (photo,mydate) VALUES ('D10-05-0052 Plenty of Light 04.JPG','4/29/2005')

The second query always returns an ASP error:

Microsoft OLE DB Provider for SQL Server error '80040e37'

Invalid object name '#p601'.

It seems the temporary table is expiring. But why?!?!

 

[darrellblackhawk]

I'm not sure what your problem is. I don't have any problems creating the temporary tables.

Maybe a dumb question, but are you sure you're creating #p601?

Darrell