none
Problem referencing a global temporary table

    Pergunta

  • 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?

    terça-feira, 9 de maio de 2006 16:12

Respostas

  • 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.
    quarta-feira, 10 de maio de 2006 01:04

Todas as Respostas

  • 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
    ---
    terça-feira, 9 de maio de 2006 18:56
    Moderador
  • 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.
    terça-feira, 9 de maio de 2006 21:08
  • 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.
    quarta-feira, 10 de maio de 2006 01:04
  • 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."

    segunda-feira, 12 de fevereiro de 2007 09:37