Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2002

    Unanswered: Cannot drop Temporary Table linked to Connection

    Hi there!

    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?



  2. #2
    Join Date
    Feb 2002
    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)?

  3. #3
    Join Date
    Apr 2002
    United Kingdom
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts