Unanswered: Cannot drop Temporary Table linked to Connection
I have a Java-Servlet that opens a connection to a SQL-Server 2000
With this connection I create a temporary local table.
I can call the temporary table within this connection as often as I want ... it's there as long as the connection is open. It's not dropped automatically as long as the connection is open. But when want to drop that table MANUALLY, I get a
[Microsoft][SQLServer JDBC Driver][SQLServer]Cannot drop the table '#tablename', because it does not exist in the system catalog.
I don't use any stored procedures that could drop the table before I do.
The database is accessed only by "normal" sql-statements through the JDBC-Driver. I can read the content of the table 10 times but if I try to drop it just in the next line, I get the above error message.
Now I know that the table will be dropped as soon as I close the connection, but anyway it's safer to do it manually ... so has somebody an idea?
Do you need repeated access to the temp table or is it used just once ? Also, can you send the code you are using ? Is it straight sql or are you using a stored procedure (it appears that you are doing straight sql)?
Found this in MSDN. Look for the article "Coding T-SQL like an Expert"
IF (object_id('tempdb..#mytemp') IS not Null)
Drop Table #mytemp
Although `tempdb..#authors_temp' is a truncation of the actual physical name of the object as held in tempdb..sysobjects, somehow OBJECT_ID() recognizes that you mean "your" temporary table called #mytemp.