global temporary tables

When does a global temp table get dropped? i have a stored proc that creates
a global temp table and inserts a record into it.say for eg. i run the store
d
proc first, the temp table gets created.if another user runs the stored proc
now,the temp table is already there,hence the stored proc just uses the same
table and inserts a record into it.now if i end my session,will the temp
table still be available to the other user?
Actually what iam experiencing is, the temp table gets dropped if i end my
session even though another person is still using it.
Thanks in advance.Hi,

>From the BOL:
Global temporary tables are automatically dropped when the session that
created the table ends and all other tasks have stopped referencing
them. The association between a task and a table is maintained only for
the life of a single Transact-SQL statement. This means that a global
temporary table is dropped at the completion of the last Transact-SQL
statement that was actively referencing the table when the creating
session ended.
HTH
Barry|||> Actually what iam experiencing is, the temp table gets dropped if i end my
> session even though another person is still using it.
Yes, that is correct. The life of a global temp table is the same as the
life of the session that started it. Which is one reason why they shouldn't
be used for sharing between concurrent users. If this is your intention,
use a real table!
A

Global Temp Tables Getting Dropped form Time to Time

Hi all,

I have created several global temp tables to cache some intermediate results ...
However, it seems that after a while those tables will be dropped by SQL Server 2005 automatically (I have not restarted the server and no drop table statement ever executed against those tables). Is this a feature by design? How to make those global temp tables persistence to next service restart?

Thanks,
Ning
....maybe, but either way, you may want to consider using something other than global temp tables. Why don't you just create user tables and clear them out when you need to?|||

This is by design. ##tmp is dropped if the service is restarted and when there is no one else connects to it.

"Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended."

|||

Global temp tables are dropped after the last connection to the server closes. It is by design. As long as a connection is open, the tables persist.

If you need permenance of a table, then don't use 'Temp' tables -they are temporary...