Server作业失败的时间为“数据库中已经有一个名为'##tmp_tbl‘的对象

Question

我有一个Server 2008作业设置为每15分钟运行一次，调用一个存储过程。正常情况下，这是没有问题的，但是最近它一直在一天中的任意时间出现故障，并导致一个调用该存储过程的报告出现问题。

​

​

原始查询始终包含一个检查，如果存在，可以删除该表。

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

我还尝试创建一个具有相同参数的新过程来测试查询，并根据我在这里看到的其他问题更改查询：

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

或者将所有支票更改为：

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

但这一切只是扼杀了我创建的新测试作业，它现在平均运行45分钟，几乎每次都失败(也许我做错了？)我进行了更改并点击了execute，是否应该先禁用该作业？)

有谁知道，当##tmp_tbl在一天的大部分时间运行良好时，为什么它会失败10%-20%？

完整代码如下：

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

如果你绝对希望在开始sp之前把那张桌子放下--

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

Answer 2

对于全局临时表来说，名称##tmp_tbl太通用了，服务器上可能有另一个进程使用相同的名称，并且偶尔与您的进程重叠。

尝试将##表重命名为特定于流程的内容。

即##tmp_sp_GetGoaling_Outs

Answer 3

我知道您需要使用全局临时表，因为您是在动态sql中创建它的。但是我注意到，您正在创建并删除游标##tmp_tbl 中的游标。我建议您在光标的之外创建这个全局临时表，如下所示

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

create table ##temp_tbl (....)

在游标内部，您需要重写动态查询，而不是使用

select .. into ##temp_tbl from ...

使用

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

我怀疑当尝试在##temp_tbl中执行select *时，您看到的错误可能在游标内。