SQL Server Performance

Failed to access temp table

Discussion in 'SQL Server DTS-Related Questions' started by beaniebear, Mar 19, 2007.

  1. beaniebear New Member

    I create a DTS package, use one connection to create and populate temp table with ids to work with in next step, as following:
    declare @lastTimeUpdated datetime, @lastTimeExec datetime
    declare @tablename char(20)
    set @tablename='Sys_Function'
    select @lastTimeUpdated = max(Function_created) from Sys_Function
    if (select count(*) from Trans_Upd_Time_Exec_Log where tablename=@tablename)=0
    begin
    insert into Trans_Upd_Time_Exec_Log (Tablename,NewExcTime,LstExcTime ) values(@tablename,getdate(),getdate())
    select @lastTimeExec = getdate()
    end
    else
    begin
    select @lastTimeExec = LstExcTime from Trans_Upd_Time_Exec_Log where Tablename=@tablename
    update Trans_Upd_Time_Exec_Log set NewExcTime = @lastTimeUpdated where Tablename=@tablename
    end

    -- Populate Table
    select
    [Function_id]
    into
    ##Tmp_ID_Tbl5
    from
    Sys_Function
    where (Function_created>=@lastTimeExec ) and
    (Function_created<=@lastTimeUpdated )

    And then I use another connection to delete rows to be updated where ids are in temp table
    delete
    from
    Sys_Function
    where
    [Function_id] in
    (
    select id from ##Tmp_ID_Tbl5
    )

    When executing this step, it shows invalid object name '##Tmp_ID_Tbl5'.
    Is anybody having any idea of this error message?

    Thanks,

  2. Luis Martin Moderator

    Why do you use ## for temp table instead single #?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  3. beaniebear New Member

    I want to create a global temp table.

    Thanks,
  4. MohammedU New Member

    Global temp tables are visible by all users... but Global temporary tables are automatically dropped when the session that created the table ends....

    May be the connection created your global temp table not exists any more...


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  5. Adriaan New Member

    quote:Global temporary tables are automatically dropped when the session that created the table ends....
    Incorrect: a global temp table gets dropped when the last session that accesses the table ends.

Share This Page