temporary table

temporary table

• Sugan

  This may be because, temp tables are session specific and session is still active. So add these lines , before creation of temptable and at the end of StoredProcedure. Basically, what we are doing here is  droping  the table at the end of storedprocedure exclusively.

  if object_id('Tempdb..#Sometablename') is not null

  begin

  Drop table #Sometablename

  end

   

  Madhu

   

  
  
  

• DanMeyers

  when i run query :

  select * from sysobjects where name like '#tmp%'

  i get all the  information  of all the temp table

  when i run :

  SELECT * FROM master..sysprocesses   where status = 'sleeping'

  SELECT *

  FROM sys.dm_exec_sessions

  where status = 'sleeping'

  i can not  find the connection  between spid,seession and temp table

   

   

  
  

• Cem DEMiRKIR

  hi

  i just can't drop the #temp because the table created yesterday by store procedure .

  when the procedure run ,maybe without droping the table #temp .

  after finish executing , the table must be droped automatic !!!!

  today i see the #temp in tempdb database and cannot return to the session which

  the table was created !!! becaude using drop in a new session does not work !!!

  
  

• XNA Rockstar

  the problem is that i see the #tmp table with the object explorer in tempdb..temporary_tables

  but i lost the session that executed the store procedure that missed the drop from some reason !!!

  now i can drop the table only from the lost session

  so maybe i cant restore the session .

  is there another way to reach the temporary table

  
  

• Martin Lundberg

  Missed the last notification of your post, Madhu is right, you should alway be clean with your coding, removing things if they are not needed anymore, that also applies to frontend as well as backend coding. If you want to get rid of the temp table now (appearantly the session is still open) you will hae to change the context to the temp database and query the sysobjects table to identify the internal used named for the table, normally something like #SomeTable_____________________________________________________SomeNumber, copy and paste this code to your Drop table statement and you’ll be fine for this time.
  
  HTH, Jens K. Suessmeyer.
  
  ---
  http://www.sqlserver2005.de
  ---
  

• GeeMann

  have u read Suessmeyer post... the procedure to drop the table is described there... otherwise just check sysprocess table for active connection... if seession are still active kill them.... then automatically these table will get droped or

  refer this link to kill sleeping session

  http://www.sql-server-performance.com/forum/topic.asp TOPIC_ID=19496

  or if this server has downtime then just restart the SQLService... it will automatically clear all temp tables. Notwithstanding, u must alter the code as i said

  Madhu

  
  
  

• Will Riley

  Temporary tables reside in the temp database, so you will have to use the command:
  
  DROP TABLE #SomeTable (although residing in the temp database)
  
  HTH; Jens K. Suessmeyer.
  
  ---
  http://www.sqlserver2005.de
  ---
  
• temporary table