March 31, 2009 at 7:11 am
What is the best way to drop temp tables at the end of T-SQL code so that when the query runs again I wont get an error stating that the ##object already exsists?
Is there a drop connection command that is normally used to disconnect the user that runs the job?
I would like to explicitly take control of this because it seems SQL Server is not dropping the user connection after the query has ran.
March 31, 2009 at 7:14 am
It sounds like you may be better off with local #temp object instead of global ##temp object.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 31, 2009 at 7:23 am
yeah u better use create tabel #temp and drop the same , rather then ##temp..
March 31, 2009 at 7:25 am
I will try that, the job runs over night so I will let you know if that works for me.
Thanks for the input.
March 31, 2009 at 7:27 am
Just to clarify. #temp objects do not have to be explicitly distroyed. they are local to the scope of execution so in your case after the query runs it's course the table will be dropped.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
March 31, 2009 at 7:32 am
yeah true, no need to write drop for #temp table, it will get dropped auto... i just forget that....sorry
March 31, 2009 at 9:40 am
You can always add the IF condition at the top
I like to explicitly drop any temp tables at end of the code as well
IF OBJECT_ID('tempdb..#table') IS NOT NULL
DROP TABLE #table
IF OBJECT_ID('tempdb..##table') IS NOT NULL
DROP TABLE ##table
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply