报错误是:
服务器: 消息 601,级别 12,状态 3,行 1
由于数据移动,未能继续以 NOLOCK 方式扫描。
服务器: 消息 823,级别 24,状态 2,过程 up_MzArtCheck,行 50
连接中断
存储过程源代码如下:
CREATE PROCEDURE up_MzArtCheck(
@as_artNo varchar(6000), --技师号码
@ad_workDate datetime, --工作日
@as_shiftNo varchar(6), --班次
@ad_dutyTime datetime, --上班时间
@ai_dutyFlag int, --[0上班/1迟到/3下班/4早退/5请假]
@as_memo varchar(60), --原因
@ai_flag int, --1、打尾牌 2、打原牌
@as_operator varchar(20)
) WITH ENCRYPTION AS
begin
set nocount on
/*******************************************************************************************
名称: up_MzArtCheck
功能: 技师报到报走
作者: Tom
日期: 2006-07-11
说明:
维护历史:
********************************************************************************************/declare @v_artNo varchar(6000)
declare @i_artNo bigint
declare @v_modeNo varchar(10)
declare @i_modeFlag int
declare @i_modeCnt int
declare @v_artName varchar(20)
declare @v_errmsg varchar(500)-----------------------------------------------------------------------------------------------
set @v_artNo = @as_artNo
truncate table MzArtArrMid
while len(@v_artNo) > 1
begin
--提取技师编码
set @i_artNo = cast( left( @v_artNo , CHARINDEX ( '#', @v_artNo ) - 1) as bigint)
set @v_artNo = SUBSTRING ( @v_artNo, CHARINDEX ( '#', @v_artNo ) + 1, 6000)
if @ai_dutyFlag < 2 --报道 插入考勤记录
begin
update MzArtInfo set i_Status = 6, v_ShiftNo = @as_shiftNo, dt_WorkDate = @ad_workDate where i_ArtNo = @i_artNo
update MzArtModes set v_GroupNo = @as_shiftNo where i_ArtNo = @i_artNo
insert into MzArtRecord (i_ArtNo, dt_WorkDate, v_ShiftNo, i_DutyFlag, v_Operator, dt_OperTime, dt_DutyTime, v_Memo)
values (@i_artNo, @ad_workDate, @as_shiftNo, @ai_dutyFlag, @as_operator, getdate(), @ad_dutyTime, @as_memo)
end
else --报走 插入考勤记录
begin
update MzArtInfo set i_Status = 3 where i_ArtNo = @i_artNo
update MzArtModes set i_OffSeqNo = i_SeqNo where i_ArtNo = @i_artNo
insert into MzArtRecord (i_ArtNo, dt_WorkDate, v_ShiftNo, i_DutyFlag, v_Operator, dt_OperTime, dt_DutyTime, v_Memo)
select i_ArtNo, dt_WorkDate, v_ShiftNo, @ai_dutyFlag, @as_operator, getdate(), @ad_dutyTime, @as_memo
from MzArtRecord where i_ArtNo = @i_artNo and dt_OperTime =
(select max(dt_OperTime) as dt_OperTime from MzArtRecord where i_ArtNo = @i_ArtNo)
continue
end
set @i_modeFlag = 0
select @i_modeCnt = count(*) from MzArtModes where i_ArtNo = @i_artNo --取得服务项目数[循环次数]
if @i_modeCnt is null or @i_modeCnt = 0
begin
select @v_artName = v_WorkerNo from MzArtInfo where i_ArtNo = @i_artNo
set @v_errmsg = '[' + @v_artName + ']技师没有设置服务项目!'
raiserror 50001 @v_errmsg
end
declare get_Mode cursor for select v_ModeNo from MzArtModes where i_ArtNo = @i_artNo
open get_Mode
fetch next from get_Mode into @v_ModeNo
while @i_modeFlag < @i_modeCnt
begin
if @ai_flag = 1
execute up_MzArtAdjust @i_artNo, @v_modeNo, 'last' --置尾牌
else
execute up_MzArtAdjust @i_artNo, @v_modeNo, 'origin' --置原牌
set @i_modeFlag = @i_modeFlag + 1
fetch next from get_Mode into @v_ModeNo
end
close get_Mode
deallocate get_Mode
endset nocount off
end
服务器: 消息 601,级别 12,状态 3,行 1
由于数据移动,未能继续以 NOLOCK 方式扫描。
服务器: 消息 823,级别 24,状态 2,过程 up_MzArtCheck,行 50
连接中断
存储过程源代码如下:
CREATE PROCEDURE up_MzArtCheck(
@as_artNo varchar(6000), --技师号码
@ad_workDate datetime, --工作日
@as_shiftNo varchar(6), --班次
@ad_dutyTime datetime, --上班时间
@ai_dutyFlag int, --[0上班/1迟到/3下班/4早退/5请假]
@as_memo varchar(60), --原因
@ai_flag int, --1、打尾牌 2、打原牌
@as_operator varchar(20)
) WITH ENCRYPTION AS
begin
set nocount on
/*******************************************************************************************
名称: up_MzArtCheck
功能: 技师报到报走
作者: Tom
日期: 2006-07-11
说明:
维护历史:
********************************************************************************************/declare @v_artNo varchar(6000)
declare @i_artNo bigint
declare @v_modeNo varchar(10)
declare @i_modeFlag int
declare @i_modeCnt int
declare @v_artName varchar(20)
declare @v_errmsg varchar(500)-----------------------------------------------------------------------------------------------
set @v_artNo = @as_artNo
truncate table MzArtArrMid
while len(@v_artNo) > 1
begin
--提取技师编码
set @i_artNo = cast( left( @v_artNo , CHARINDEX ( '#', @v_artNo ) - 1) as bigint)
set @v_artNo = SUBSTRING ( @v_artNo, CHARINDEX ( '#', @v_artNo ) + 1, 6000)
if @ai_dutyFlag < 2 --报道 插入考勤记录
begin
update MzArtInfo set i_Status = 6, v_ShiftNo = @as_shiftNo, dt_WorkDate = @ad_workDate where i_ArtNo = @i_artNo
update MzArtModes set v_GroupNo = @as_shiftNo where i_ArtNo = @i_artNo
insert into MzArtRecord (i_ArtNo, dt_WorkDate, v_ShiftNo, i_DutyFlag, v_Operator, dt_OperTime, dt_DutyTime, v_Memo)
values (@i_artNo, @ad_workDate, @as_shiftNo, @ai_dutyFlag, @as_operator, getdate(), @ad_dutyTime, @as_memo)
end
else --报走 插入考勤记录
begin
update MzArtInfo set i_Status = 3 where i_ArtNo = @i_artNo
update MzArtModes set i_OffSeqNo = i_SeqNo where i_ArtNo = @i_artNo
insert into MzArtRecord (i_ArtNo, dt_WorkDate, v_ShiftNo, i_DutyFlag, v_Operator, dt_OperTime, dt_DutyTime, v_Memo)
select i_ArtNo, dt_WorkDate, v_ShiftNo, @ai_dutyFlag, @as_operator, getdate(), @ad_dutyTime, @as_memo
from MzArtRecord where i_ArtNo = @i_artNo and dt_OperTime =
(select max(dt_OperTime) as dt_OperTime from MzArtRecord where i_ArtNo = @i_ArtNo)
continue
end
set @i_modeFlag = 0
select @i_modeCnt = count(*) from MzArtModes where i_ArtNo = @i_artNo --取得服务项目数[循环次数]
if @i_modeCnt is null or @i_modeCnt = 0
begin
select @v_artName = v_WorkerNo from MzArtInfo where i_ArtNo = @i_artNo
set @v_errmsg = '[' + @v_artName + ']技师没有设置服务项目!'
raiserror 50001 @v_errmsg
end
declare get_Mode cursor for select v_ModeNo from MzArtModes where i_ArtNo = @i_artNo
open get_Mode
fetch next from get_Mode into @v_ModeNo
while @i_modeFlag < @i_modeCnt
begin
if @ai_flag = 1
execute up_MzArtAdjust @i_artNo, @v_modeNo, 'last' --置尾牌
else
execute up_MzArtAdjust @i_artNo, @v_modeNo, 'origin' --置原牌
set @i_modeFlag = @i_modeFlag + 1
fetch next from get_Mode into @v_ModeNo
end
close get_Mode
deallocate get_Mode
endset nocount off
end
当使用 NOLOCK 锁定提示或设置为 READ UNCOMMITTED 的事务隔离级别扫描时,可能会删除扫描当前位置处的页。当这种情况发生时,Microsoft® SQL Server™ 将无法继续扫描。对策
该错误终止查询。重新提交查询或删除 NOLOCK 锁定提示。