SQL Server job failing 10-20% of the time for "there is already an object named '##tmp_tbl' in the DB

Question

I have a SQL Server 2008 job set to run every 15 minutes, calling a stored procedure. Normally this runs without issue, but lately it has been failing at random times throughout the day and causing issues with a report that is calling that stored procedure.

The original query has always contained a check to drop the table if exists

IF (SELECT OBJECT_ID('tempdb..##tmp_tbl')) IS NOT NULL
    DROP TABLE ##tmp_tbl

I also tried creating a new procedure with the same parameters to test the query and changing it based on other questions I have seen here:

IF SELECT OBJECT_ID('tempdb..##tmp_tbl') IS NOT NULL
BEGIN
    DROP TABLE ##tmp_tbl
END

Or by changing all of the check's to:

IF (SELECT OBJECT_ID('tempdb..##tmp_tbl')) IS NOT NULL
    DROP TABLE ##tmp_tbl

but all this did was kill the new test job I created, it now runs for an average of 45 minutes and fails almost every time (maybe I did it wrong? I made the change and hit execute, should I have disabled the job first?)

Does anyone know why this would fail 10-20% of the time for a ##tmp_tbl when it runs fine most of the day?

Full code below:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_GetGoaling_Outs]
    @site varchar(4)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sqlStr nvarchar(max)
    DECLARE @sqlStr2 nvarchar(max)
    DECLARE @openQueryStr nvarchar(max)
    DECLARE @so nvarchar(15)
    DECLARE @goalDate nvarchar(10)
    DECLARE @wc nvarchar(35)
    DECLARE @wc2 nvarchar(35)
    DECLARE @goal_yield smallint
    DECLARE @early_goal_date nvarchar(10)
    DECLARE @early_goal_yield smallint

     --Declare @site varchar(4)
     --set @site = 'OR01'
     --[sp_GetGoaling_Outs] 'OR01'

        --set @so = '147300'
        --set @goalDate = '2016/02/24'



IF (SELECT OBJECT_ID('tempdb..##tmp_tbl')) IS NOT NULL
DROP TABLE ##tmp_tbl


IF EXISTS (SELECT TOP 1 * FROM GoalTemp)  --If the Goal Temp Table is empty, then do not run
BEGIN

    TRUNCATE TABLE Goal
    INSERT INTO GOAL (shop_order,work_center, goal_yield, goal_date, early_goal_yield, early_goal_date)
        SELECT shop_order,work_center, goal_yield, goal_date, early_goal_yield, early_goal_date
        FROM GOALTemp



    DECLARE db_cursor CURSOR FOR  
    SELECT shop_order, work_center, goal_date, goal_yield, early_goal_yield, early_goal_date
    FROM Goal
    --WHERE goal_date >  DATEADD(mm,-2,GETDATE())

    OPEN db_cursor  
    FETCH NEXT FROM db_cursor INTO @so, @wc, @goalDate, @goal_yield, @early_goal_yield, @early_goal_date

    WHILE @@FETCH_STATUS = 0  
    BEGIN  

    SET @wc2 = 'BDL_' + @wc
    ----------------------------------------GET Yield (Outs) for work center to current date AND to end goal date-------------------------------------- 

    set @sqlStr = 'SELECT site, shop_order, work_center, goal_date, wc_outs, 
            (SELECT WC_OUTS_TO_NEED_DATE
    FROM (
     --count the outs for the work center/max reporting operation seq
       SELECT DISTINCT  
             COUNT(*) OVER (PARTITION BY shop_order, work_center ORDER BY shop_order )  WC_OUTS_TO_NEED_DATE
       FROM (
                SELECT *
               FROM (
                     --get the work center, reporting operation seq, and max reporting op seq for the route 
                     SELECT base.site, base.shop_order,base.sfc, base.router, base.router_revision, 
            base.completeDateTime ,base.operation, base.work_center, cf.value rep_op_seq
            , row_number() over (partition by base.shop_order, base.sfc, base.router, base.router_revision, base.operation, base.work_center order by base.shop_order) rownumber                                
            , MAX(cf.value) OVER (PARTITION BY base.work_center ) AS max_wc_op_rpt_seq 
            , MAX(cf.value) OVER (PARTITION BY base.work_center )+1 AS max_wc_op_rpt_seqPlus
            FROM (
                  --get only completes during the filtered time for the active shop orders
                  SELECT DISTINCT al.site, al.router, al.router_revision, al.sfc,
                  al.activity, al.reporting_center_bo
                  ,substr(al.shop_order_bo,instr( al.shop_order_bo, '','') + 1,length( al.shop_order_bo) - instr( al.shop_order_bo, '','')) shop_order 
                  ,(al.date_time + to_number(concat(substr(extract(TIMEZONE_OFFSET from systimestamp), 1, 1), substr(extract(TIMEZONE_OFFSET from systimestamp), 12, 2))) / 24 ) completeDateTime  
                  , substr(o.reporting_center_bo,instr(o.reporting_center_bo, '','') + 1,length(o.reporting_center_bo)) work_center
                  , o.handle oHandle
                  , o.operation 
                  FROM
                    wip.activity_log al,
                    wip.operation o
                  WHERE
                    al.action_code = ''COMPLETE''
                    AND al.operation = o.operation
                    AND al.site = ''' + @site + '''

                    AND trunc(al.date_time + to_number(concat(substr(extract(TIMEZONE_OFFSET from systimestamp), 1, 1), substr(extract(TIMEZONE_OFFSET from systimestamp), 12, 2))) / 24 )
                        <= TO_DATE(''' + @goalDate + ''',''yyyy/mm/dd'')

                    AND substr(al.shop_order_bo,instr( al.shop_order_bo, '','') + 1,length( al.shop_order_bo) - instr( al.shop_order_bo, '',''))
                                    = ''' + @so + ''' 
              ) base
              ,wip.router r, wip.router_step rs, wip.router_operation ro
              ,(SELECT SUBSTR(handle,instr(handle, '','') + 1,length(handle) - instr(handle, '','') - 2) operation, attribute, value
                    FROM wip.custom_fields 
                    WHERE attribute = ''REPORT_OP_SEQUENCE'' ) cf
              WHERE   
              base.router = r.router (+)    
              AND base.router_revision = r.revision (+)          
              AND r.handle = rs.router_bo (+)
              AND rs.handle = ro.router_step_bo (+)
              AND substr( ro.operation_bo,1,length(ro.operation_bo)-2) =  substr( base.oHandle,1,length(base.oHandle)-2) 
              AND base.operation = cf.operation (+)
              AND base.work_center = ''' + @wc2 + '''
           )
            WHERE rownumber = 1
        )
      WHERE rep_op_seq = max_wc_op_rpt_seq
      ) ) WC_OUTS_TO_NEED_DATE '

    SET @sqlStr2 = ' FROM (
      SELECT DISTINCT  site, shop_order
      , work_center, ''' + @goalDate + ''' as goal_date,
            COUNT(*) OVER (PARTITION BY shop_order, work_center ORDER BY shop_order )  WC_OUTS
      FROM (
                SELECT *
                 FROM (
                    --get the work center, reporting operation seq, and max reporting op seq for the route 
                    SELECT base.site, base.shop_order,base.sfc, base.router, base.router_revision, 
                    base.completeDateTime ,base.operation, base.work_center, cf.value rep_op_seq
                    , row_number() over (partition by base.shop_order, base.sfc, base.router, base.router_revision, base.operation, base.work_center order by base.shop_order) rownumber  
                    , MAX(cf.value) OVER (PARTITION BY base.work_center ) AS max_wc_op_rpt_seq 
                    , MAX(cf.value) OVER (PARTITION BY base.work_center )+1 AS max_wc_op_rpt_seqPlus
                    FROM (
                           --get only completes during the filtered time for the active shop orders
                           SELECT DISTINCT al.site, al.router, al.router_revision, al.sfc,
                          al.activity, al.reporting_center_bo
                                   ,substr(al.shop_order_bo,instr( al.shop_order_bo, '','') + 1,length( al.shop_order_bo) - instr( al.shop_order_bo, '','')) shop_order 
                                   ,(al.date_time + to_number(concat(substr(extract(TIMEZONE_OFFSET from systimestamp), 1, 1), substr(extract(TIMEZONE_OFFSET from systimestamp), 12, 2))) / 24 ) completeDateTime  
                           , substr(o.reporting_center_bo,instr(o.reporting_center_bo, '','') + 1,length(o.reporting_center_bo)) work_center
                         , o.handle oHandle
                         , o.operation                                        
                  FROM
                    wip.activity_log al,
                    wip.operation o
                  WHERE
                   al.action_code = ''COMPLETE''
                   AND al.operation = o.operation
                   AND al.site = ''' + @site + '''
                            --  AND trunc(al.date_time + to_number(concat(substr(extract(TIMEZONE_OFFSET from systimestamp), 1, 1), substr(extract(TIMEZONE_OFFSET from systimestamp), 12, 2))) / 24 )
                            --  <= TO_DATE(''' + @goalDate + ''',''yyyy/mm/dd'')

                    AND substr(al.shop_order_bo,instr( al.shop_order_bo, '','') + 1,length( al.shop_order_bo) - instr( al.shop_order_bo, '',''))
                                    = ''' + @so + ''' 
              ) base
              ,wip.router r  , wip.router_step rs, wip.router_operation ro
              ,(SELECT SUBSTR(handle,instr(handle, '','') + 1,length(handle) - instr(handle, '','') - 2) operation, attribute, value
                    FROM wip.custom_fields 
                    WHERE attribute = ''REPORT_OP_SEQUENCE'' ) cf
              WHERE   
              base.router = r.router (+)    
              AND base.router_revision = r.revision (+)       
              AND r.handle = rs.router_bo (+)
              AND rs.handle = ro.router_step_bo (+)
              AND substr( ro.operation_bo,1,length(ro.operation_bo)-2) =  substr( base.oHandle,1,length(base.oHandle)-2) 
              AND base.operation = cf.operation (+)
              AND base.work_center  = ''' + @wc2 + '''
         )
                 WHERE rownumber = 1                          
       )
      WHERE rep_op_seq = max_wc_op_rpt_seq
    )'


             SET @openQueryStr = 'select * into ##tmp_tbl FROM OPENQUERY(WIP, ''' + REPLACE(@sqlStr, '''', '''''') + REPLACE(@sqlStr2, '''', '''''') + ''')' 
             EXEC(@openQueryStr)    
        --print  @sqlStr
        --print  @sqlStr2


                UPDATE goal
                SET actual_yield = t.wc_outs,
                actual_yield_to_need_date = t.WC_OUTS_TO_NEED_DATE
                FROM goal g inner join ##tmp_tbl t ON g.shop_order = t.shop_order
                    AND g.work_center = Right(t.work_center, LEN(t.work_center)-4)
                    AND g.goal_date = t.goal_date   





    ---------------------------IF THERE IS AN EARLY GOAL, THEN GET THE OUTS FOR THAT GOAL up to the early goal date--------------

    IF (@early_goal_date IS NOT NULL)
    BEGIN
            IF OBJECT_ID('tempdb..##tmp_tbl') IS NOT NULL
            DROP TABLE ##tmp_tbl


    set @sqlStr =  'SELECT DISTINCT  site, shop_order
    , work_center, ''' + @early_goal_date + ''' as goal_date,
        COUNT(*) OVER (PARTITION BY shop_order, work_center ORDER BY shop_order )  WC_OUTS
    FROM (
           SELECT *
           FROM (
                        SELECT base.site, base.shop_order, base.sfc, base.router, base.router_revision, 
                        base.completeDateTime ,base.operation, base.work_center, cf.value rep_op_seq
                        , row_number() over (partition by base.shop_order, base.sfc, base.router, base.router_revision, base.operation, base.work_center order by base.shop_order) rownumber                                    
                        , MAX(cf.value) OVER (PARTITION BY base.work_center ) AS max_wc_op_rpt_seq 
                        , MAX(cf.value) OVER (PARTITION BY base.work_center )+1 AS max_wc_op_rpt_seqPlus
                         FROM (
                               SELECT DISTINCT al.site, al.router, al.router_revision, al.sfc, 
                               al.activity, al.reporting_center_bo
                                ,substr(al.shop_order_bo,instr( al.shop_order_bo, '','') + 1,length( al.shop_order_bo) - instr( al.shop_order_bo, '','')) shop_order 
                                ,(al.date_time + to_number(concat(substr(extract(TIMEZONE_OFFSET from systimestamp), 1, 1), substr(extract(TIMEZONE_OFFSET from systimestamp), 12, 2))) / 24 ) completeDateTime  
                        , substr(o.reporting_center_bo,instr(o.reporting_center_bo, '','') + 1,length(o.reporting_center_bo)) work_center
                        , o.handle oHandle
                        , o.operation 
                      FROM
                        wip.activity_log al,
                        wip.operation o
                      WHERE
                        al.action_code = ''COMPLETE''
                        AND al.operation = o.operation
                        AND al.site = ''' + @site + '''
                        AND trunc(al.date_time + to_number(concat(substr(extract(TIMEZONE_OFFSET from systimestamp), 1, 1), substr(extract(TIMEZONE_OFFSET from systimestamp), 12, 2))) / 24 )
                        <= TO_DATE(''' + @early_goal_date + ''',''yyyy/mm/dd'')

                      AND substr(al.shop_order_bo,instr( al.shop_order_bo, '','') + 1,length( al.shop_order_bo) - instr( al.shop_order_bo, '',''))
                                        = ''' + @so + ''' 
                  ) base
                  ,wip.router r  , wip.router_step rs  , wip.router_operation ro 
                  ,(SELECT SUBSTR(handle,instr(handle, '','') + 1,length(handle) - instr(handle, '','') - 2) operation, attribute, value
                        FROM wip.custom_fields 
                        WHERE attribute = ''REPORT_OP_SEQUENCE'' ) cf
                  WHERE   
                  base.router = r.router (+)    
                  AND base.router_revision = r.revision (+)    
                  AND r.handle = rs.router_bo (+)
                  AND rs.handle = ro.router_step_bo (+)
                  AND substr( ro.operation_bo,1,length(ro.operation_bo)-2) =  substr( base.oHandle,1,length(base.oHandle)-2) 
                  AND base.operation = cf.operation (+)
                  AND base.work_center = ''' + @wc2 + '''
            )
           WHERE rownumber = 1                                    
     )
    WHERE rep_op_seq = max_wc_op_rpt_seq
    ORDER BY shop_order, work_center'


             SET @openQueryStr = N'select * into ##tmp_tbl FROM OPENQUERY(WIP, ''' + REPLACE(@sqlStr, '''', '''''') + ''')' 
             EXEC(@openQueryStr)    
            --print @sqlStr

                UPDATE goal
                SET early_actual_yield_to_need_date = t.wc_outs
                FROM goal g inner join ##tmp_tbl t ON g.shop_order = t.shop_order
                    AND g.work_center = Right(t.work_center, LEN(t.work_center)-4)
                    AND g.early_goal_date = t.goal_date
            END
    ---------------------------------------------------------------------------------------------------------                       




            IF OBJECT_ID('tempdb..##tmp_tbl') IS NOT NULL
            DROP TABLE ##tmp_tbl

            FETCH NEXT FROM db_cursor INTO @so, @wc, @goalDate, @goal_yield, @early_goal_yield, @early_goal_date
    END  

    CLOSE db_cursor  
    DEALLOCATE db_cursor

        END
    END



    --[sp_GetGoaling_Outs] 'OR01'

Answer 1

If you absolutely want that table dropped before beginning the sp--

BEGIN TRY  
  drop table ##tmp_tbl
END TRY  
BEGIN CATCH  
  /* table does not exist */
END CATCH  

Answer 2

The name ##tmp_tbl is too generic for a Global Temp table, there is probably another process on the server that is using the same name and occasionally overlapping with your process.

Try renaming the ## table to something that is specific to your process

i.e ##tmp_sp_GetGoaling_Outs

Answer 3

I understand that you need to use global temp table because you are creating it inside a dynamic sql. But I notice that you are creating and dropping the ##tmp_tbl inside a cursor. I would recommend that you create this global temp table outside of the cursor, such as the following

if object_id('tempdb..##temp_tbl') is not null
   drop table ##temp_tbl;

create table ##temp_tbl (....)

Inside your cursor, you need to re-write your dynamic query, instead of using

select .. into ##temp_tbl from ...

using

truncate table ##temp_tbl; 
insert into ##temp_tbl (...) 
select ... from

I suspect the error you see is probably inside the cursor when trying to do the select * into ##temp_tbl.