I've also tried this on SQL Server 2000 and it behaves the same. Haven't tried it on SQL Server 2008 though.


Legacy Comments






Jason Massie

 
2008-08-21
re: SQL Server 2005 temporary tables bug, feature or expected behavior? 
 Well, the # table works if you separate the drop\create's by a GO. I am not sure why though. SQL may not immediately clean up temp drops.






Mladen

 
2008-08-21
re: SQL Server 2005 temporary tables bug, feature or expected behavior? 
 yes it works if you separate then with GO. but for example if you have this in a sproc GO is not an option. let's not dwell on why you'd have it in a sproc :))






Adam Machanic

 
2008-08-21
re: SQL Server 2005 temporary tables bug, feature or expected behavior? 
 The error seems to be happening during the parse/compile phase.  Notice that the SELECT 'Temporary table' is not returned, nor are any rows affected messages.  This seems like a bug; I would submit a Connect item if I were you.







Mladen

 
2008-08-21
re: SQL Server 2005 temporary tables bug, feature or expected behavior? 
 i will submit the connect item. thanx.






Simon

 
2008-08-21
re: SQL Server 2005 temporary tables bug, feature or expected behavior? 
 That is interesting.  It seems that the initial parse of the code is failing when it sees the temp table created twice in the same batch.  This could be a restriction implemented by the engine on temp tables because of clean up issues.
If you put the statements into 2 batches it works fine.  
If you add a WAITFOR instead of a batch separator it still won't execute and returns the error message.






Simon

 
2008-08-21
re: SQL Server 2005 temporary tables bug, feature or expected behavior? 
 Looking in BOL - I found this
"If more than one temporary table is created inside a single stored procedure or batch, they must have different names."

So I guess it's not a bug after all(?).  Seems to be by design that temp tables cannot have the same name within a batch.






Mladen

 
2008-08-21
re: SQL Server 2005 temporary tables bug, feature or expected behavior? 
 nice! care to tell me where you found this?






Mladen

 
2008-08-21
re: SQL Server 2005 temporary tables bug, feature or expected behavior? 
 excelent! thanx. i missed that completly.






Adam Machanic

 
2008-08-21
re: SQL Server 2005 temporary tables bug, feature or expected behavior? 
 Thanks for sharing, Simon.  Weird little restriction.  Easy enough to work around, luckily!






Peso

 
2008-08-22
re: SQL Server 2005 temporary tables bug, feature or expected behavior? 
 Anything related to this issue?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=108889







Peso

 
2008-08-22
re: SQL Server 2005 temporary tables bug, feature or expected behavior? 
 You can simplify nd narrow down the sitution down to this

if 1= 1 
create table #test (i  int)
else
create table #test (j varchar(2))







Mladen

 
2008-08-22
re: SQL Server 2005 temporary tables bug, feature or expected behavior? 
 yes that's true Peso, 
but the thing that is weird for me is that the 
drop table #tempTable
should make a difference.
oh well...






Peso

 
2008-08-22
re: SQL Server 2005 temporary tables bug, feature or expected behavior? 
 I think the reason for normal tables arae working and temporary table are not working are due to scope and/or persistancy.
Local temporary tables are only accessible to you, and normal tables are accessible to all.
Have you tried running the code again, and use global temporary tables, ##Test?

I have a hunch that they will act as temporary tables even if they are accessible to all, but they are not persistent (as well as local temporary tables are not persistent).







Mladen

 
2008-08-22
re: SQL Server 2005 temporary tables bug, feature or expected behavior? 
 global temp tables also error out.






dbDan

 
2008-08-27
re: SQL Server 2005 temporary tables bug, feature or expected behavior? 
 I agree, it's happening in the pre-compile stage. 

Add a delay between the two temp code segments and its more apparent.
WAITFOR DELAY '00:00:05'

I've noticed similar issues in aspx, where javascript comments are looked at by the pre-compiler, which promptly complains about a non-issue, because it doesn't know they are comments.

I think in cases like this, it comes down to someones idea of a performance tweak, intended to imporove the "user experience", at the cost of leaving us scraching our heads, searching for anything remotly related, trying some test cases to make sense of it, and finally mumbling a few choice words once it does.









Stewart

 
2008-08-27
re: SQL Server 2005 temporary tables bug, feature or expected behavior? 
 I have had a similar issue in the past with 2000.  Doing something like :

IF <condition>
  SELECT * INTO #Temp
ELSE
  SELECT * INTO #Temp

fails to compile with 'there already is an object ...'







Steve

 
2008-08-27
re: SQL Server 2005 temporary tables bug, feature or expected behavior? 
 Not sure where the problem is....  I've run the following code on SQL Server 2005 64-bit, and it works correctly every time, whereas I would expect to see the error you encountered:

[code]
SELECT CAST(1 AS int) AS THE_INT
INTO #tempTable

IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL
	BEGIN 
    SELECT 'DROP Temporary table'
    DROP TABLE #tempTable
	END
IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL
	BEGIN 
    SELECT 'DROP Temporary table'
    DROP TABLE #tempTable
	END
ELSE
	BEGIN
	SELECT 'NO DROP NEEDED'
	END
[/code]

Steve
(aka smunson)
:):):)






Mladen

 
2008-08-27
re: SQL Server 2005 temporary tables bug, feature or expected behavior? 
 acctually steve there's no reason why your code wouldn't work. it does work just fine.
however i don't see the code's point in regard to my example.
or were you replying to someone else?







ScrappyLaptop

 
2008-09-03
re: SQL Server 2005 temporary tables bug, feature or expected behavior? 
 
Whenever you create a local temp table, even though you may call it something like "#tempTable", the entry in temdb..sysobjects is actually called something like "#tempTable_____________________________________________________________
_____________________________________________000000008475" 
in the name field.

Try this:
USE tempdb
SELECT * FROM sysobjects
WHERE name LIKE '%#%'

By appending a long string of underscores and a numeric identifier, SQL Server can allow different sessions to appear to use the same name for a temp table.  Sort of.  The problem is in the translation between what *you* call the temp table and what SQL Server calls it in the name field of tempdb..sysobjects.

To workaround this limitation, within a session, you have to query and specify the full name of #tempTable to match the one just created in your session, then delete it.  






JohnHorb

 
2008-09-05
re: SQL Server 2005 temporary tables bug, feature or expected behavior? 
 From the SYBASE Transact-SQL Manual

"Manipulating temporary tables in stored procedures
Stored procedures can reference temporary tables that are created during the current session. Within a stored procedure, you cannot create a temporary table, drop it, and then create a new temporary table with the same name." 








Simon Sabin

 
2009-01-03
re: SQL Server 2005 temporary tables bug, feature or expected behavior? 
 I would guess its an optimisation that enables the whole procedure to be compiled at the start of the batch. 

Because only you can change temp tables it can do this optimisation, unlike normal tables where it couldn't because someone else may have dropped/created the object during the execution of the procedure and so they have to defer checking until runtime.

Having the procedure compile at the start avoids painful recompilations during the procedure.






Mark Wills

 
2009-04-27
re: SQL Server 2005 temporary tables bug, feature or expected behavior? 
 Think it is "just" the compiler, because the temp table is definitely not there at runtime... 

To trick it, using global temptable, make it hard for the pre-compile part to complain and see what happens at runtime. For example the following works fine with all the expected messages indicating that the table is not there at runtime. So, it is a bug of sorts, and likely because it is not properly checking for the object (which it cannot do until runtime because it is a temp table and the real name is yet to be determined).

IF OBJECT_ID('tempdb..##tempTable') IS NOT NULL DROP TABLE ##tempTable 

SELECT 1 as attempt,* INTO ##tempTable FROM [master]..spt_values
SELECT * FROM ##tempTable
DROP TABLE ##tempTable

IF OBJECT_ID('tempdb..##tempTable') IS NOT NULL SELECT 'Need to DROP' ELSE SELECT 'No Need to DROP'

exec ('SELECT 2 as attempt,* INTO ##tempTable FROM [master]..spt_values')
SELECT * FROM ##tempTable
DROP TABLE ##tempTable