From: tfs@dslextreme.com   
      
   Both reponses make sense.   
      
   Thanks,   
      
   Tom   
   "Erland Sommarskog"  wrote in message   
   news:Xns9E57F0A0CFF8DYazorman@127.0.0.1...   
   > tshad (tfs@dslextreme.com) writes:   
   >> 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?   
   >   
   > Way back in the dim and distant past, in 6.5 and previous versions,   
   > this procedure would not compile:   
   >   
   >   CREATE PROCEDURE bad_sp AS   
   >   CREATE TABLE #temp (a int NOT NULL)   
   >   SELECT b FROM #temp   
   >   
   > You could get an error when you tried to create the procedure that b   
   > is a non-existing column.   
   >   
   > Today, you don't get this error, because the temp table does not exist   
   > when the procedure is created, and then it blows up at run-time. Deferred   
   > name resolution, a horrible misfeature.   
   >   
   > But as you can see in SQL 6.5, SQL Server extracted the temp-table   
   > definitions, and validated queries with temp tables. As you realise,   
   > this would cause a bit of a problem if the procedure would look like   
   > this:   
   >   
   >   CREATE PROCEDURE even_worse_sp @a int AS   
   >   IF @a = 1   
   >      CREATE TABLE #temp (a int NOT NULL)   
   >   ELSE   
   >      CREATE TABLE #temp (c int NOT NULL)   
   >   SELECT c FROM #temp   
   >   
   > How would SQL 6.5 to compile this? It had but one choice: to outlaw, which   
   > it did. With the very same error message, you got.   
   >   
   > With SQL 7, deferred name resolution was introduced, and there was nothing   
   > in the above that could confuse compilation. The procedure would or would   
   > not blow up at run-time.   
   >   
   > But nevertheless, the check and the restriction was retained. Why? An   
   > oversight? Inconsistency is after all the hailmark of SQL Server!   
   >   
   > Anyway, use CREATE TABLE instead. Then you only need it once. Use   
   > TRUNCATE TABLE to wipe out all rows.   
   >   
   > --   
   > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
   >   
   > Links for SQL Server Books Online:   
   > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
   >   
   >   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)