Does Commit Transaction delete Temporary table?

11 Nov 2005 5:52 PM

adi

Hi SQL Guru's,
I create a global temp table in one of my procedures:

select * into ##temp_update
from ( select distinct * from ......

after this I issue a commit transaction, and when I try to select from
the table I created (from my .Net code or Query Analyzer) it is not
there!

select * from ##temp_update
Invalid object name '##temp_update'.

Any idea why?

11 Nov 2005 6:01 PM

ML

From Books Online:

"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."

If the session that creates the global temporary table ends before a new
session that accesses the global temporary table is started, the table is
dropped.

ML

11 Nov 2005 6:05 PM

adi

Yikes!

Good to know that, but bad for my program :)

thanks much

ML wrote:
Show quote

> From Books Online:
>
> "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."
>
> If the session that creates the global temporary table ends before a new
> session that accesses the global temporary table is started, the table is
> dropped.
>
>
> ML