From: hugo@perFact.REMOVETHIS.info.INVALID   
      
   On Thu, 23 Dec 2010 11:25:20 -0800, "tshad"    
   wrote:   
      
   >I am trying to drop and reuse a temporary table and I get an error on the   
   >second select/into.   
   >   
   >*******************************************   
   >IF OBJECT_ID('tempDB..#tfs1') IS NOT NULL   
   > DROP TABLE #tfs1   
   >   
   >SELECT * INTO #tfs1 ...   
   >   
   >...   
   >   
   >DROP TABLE #tfs   
   >   
   >SELECT * INTO #tfs1   
   >********************************************   
   >   
   >This gives me an error:   
   >   
   >     There is already an object named '#tfs1' in the database.   
   >   
   >But this seems to be done before actually executing the query.   
   >   
   >Why would that be?   
   >   
   >Obviously it is not there as I dropped it.  I originally had the "IF   
   >OBJECT_ID..." that I had before the first drop also before the 2nd drop and   
   >thought that maybe that was a problem.  So I just dropped it without testing   
   >for it.   
      
   Hi Tom,   
      
   This is a known limitation, which happens due to how the parser works   
   in relation to temporary tables. (If you do the same for a normal   
   table, you won't get an error).   
      
   It's also documented. In Books Online, in the topic about CREATE   
   TABLE, you'll find this remark:   
   "If more than one temporary table is created inside a single stored   
   procedure or batch, they must have different names."   
      
   The possible workarounds are:   
      
   1. Remove the DROP TABLE and change the second SELECT ... INTO #tfs1   
   in an INSERT INTO #tfs1 SELECT ... statement; or   
      
   2. Use two different temp table names.   
   --   
   Hugo Kornelis, SQL Server MVP   
   My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)