Problem referencing a global temporary table

I'm having difficulty referencing a global temporary table in a stored procedure. My stored procedure will execute correctly for days on end, but then all of a sudden it will stop working and I will get messages like:

Invalid object name '##TableName'.

and

Cannot drop the table '##TableName, because it does not exist in the system catalog.

==========================================================================

In my stored procedure I am first creating my temporary table using the syntax below. I need to create a seed value from a parameter, so that is why I am using the SET @.SQL and EXEC(@.SQL) statements:

SET @.SQL =
'CREATE TABLE ##TableName (
[Value1] [varchar] (11) ,
[Value2] [numeric](13, 0) ,
[Value3] [varchar] (30) ,
[Value4] [varchar] (30) ,
[Value5] [int] IDENTITY (' + CAST(@.Variable AS VARCHAR(10)) + ', 1) NOT NULL )'
EXEC(@.SQL)

I then begin a transaction and perform an INSERT INTO statement into this temp table, and then later perform a SELECT statement, and finally a DROP statement.

Any ideas or can anyone point me in the right direction as to why this works SOME of the time?

Just top make sure that you know that a global temporary table can be only at one time per server. So if any other users also executes the procedure and drops the "global" temporary table, it won′t be accessible in the other session anymore. Temp Table with one dash '#' are session specific, so can created in every session. Global ones, with two dashes '##' are global created. Every user sees them and can manipulate (or even drop) them.

So I guess any other process dropped your global temporary table.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||Thank you for the info. I am aware of the difference between the global-specific temp table vs. the session-specific temp table. In fact, I chose to create the global-specific temp table so that I can reference the table after the EXEC(@.SQL) statement runs.|||

It is hard to tell what might be wrong with the code without seeing some sort of repro script. Note that if there are multiple references to a global temporary table for example, SQL Server will delete it automatically after all users referencing the table have disconnected from the server. This can lead to unpredictable behavior if you use multiple connections but doesn't seem to be the case here. In any case, you can modify the code to use just a temporary table instead by doing following:

create table #.... (

columns except the identity column

)

exec ('alter table # add ... identity column with seed')

insert into #...

select * from #...

This approach doesn't require any dynamic SQL except for the few DDLs and it is easier to read/debug also.

|||

hi!

i used some temporary table in store procedure (sqlserver 2005)

our team have report software calisto .

the calisto use crystal and reports which use

this store procedure .

because of that,

we have list of many temporary table with the same name

#dbo.sug_name ... ,#dbo.sug_name ... ,......

in the system database .

what could be the reason for that and how can we drop it ?

Msg 3701, Level 11, State 5, Line 2

Cannot drop the table '#sug_name', because it does not exist or you do not have permission."