Сбой задания SQL Server 10-20% времени для "уже есть объект с именем" ##tmp_tbl "в БД
У меня есть задание SQL 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 минут и дает сбой почти каждый раз (может быть, я ошибся? Я внес изменение и нажал "выполнить", должен ли я сначала отключить задание?)
Кто-нибудь знает, почему это не получится в 10-20% случаев ##tmp_tbl когда он работает нормально большую часть дня?
Полный код ниже:
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'
3 ответа
Решение
Если вы абсолютно хотите, чтобы эта таблица была удалена до начала sp--
BEGIN TRY
drop table ##tmp_tbl
END TRY
BEGIN CATCH
/* table does not exist */
END CATCH
Имя ## tmp_tbl слишком общее для таблицы Global Temp, возможно, на сервере существует другой процесс, который использует то же имя и иногда перекрывается с вашим процессом.
Попробуйте переименовать таблицу ## во что-то специфичное для вашего процесса
т.е. ##tmp_sp_GetGoaling_Outs
Я понимаю, что вам нужно использовать глобальную временную таблицу, потому что вы создаете ее внутри динамического 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.