在升级之前,建议先在查询分析器中执行 DBCC CHECKDB(年度库名称) 语句,检查年度库数据库是否有一致性错误,如果发现错误,请按照SQL Server的提示进行修复,修复后再进行升级。
数据库名: UFDATA_002_2015
D:\U8SOFT\Admin\SQLFILE12500\Main\Ufdata\Structure\data_str_tm_mix_nl.sql
错误信息:
-2147217900
无法从 'dbo.HR_TM_OverTimeVoucher.dAuditTime' 取消绑定。请使用 ALTER TABLE DROP CONSTRAINT。
执行如下语句时出错:
--假期额度
--hr_tm_Vacrate(假期额度)新增 nDoneVacAudited(已休(已审核请假),可为空,Number(7,2)
if not exists
(select *from syscolumns where id=OBJECT_ID('hr_tm_Vacrate') and name='nDoneVacAudited')
begin
alter table hr_tm_Vacrate add nDoneVacAudited [numeric](7,2) null
end
--hr_tm_Vacrate(假期额度)新增 nDoneVacAuditing 已休(审核中请假)可为空,Number(7,2)
if not exists
(select *from syscolumns where id=OBJECT_ID('hr_tm_Vacrate') and name='nDoneVacAuditing')
begin
alter table hr_tm_Vacrate add nDoneVacAuditing [numeric](7,2) null
end
--假期额度--
--月结果表--
--Hr_tm_MonthResult(月结果)新增 nCompTimeOffHoursAudited(调休(小时)(已审核)),可为空,Number(7,2)
if not exists
(select *from syscolumns where id=OBJECT_ID('Hr_tm_MonthResult') and name='nCompTimeOffHoursAudited')
begin
alter table Hr_tm_MonthResult add nCompTimeOffHoursAudited [numeric](7,2) null
end
--Hr_tm_MonthResult(假期额度)新增 nCompTimeOffHoursAuditing 调休(小时)(审核中) 可为空,Number(7,2)
if not exists
(select *from syscolumns where id=OBJECT_ID('Hr_tm_MonthResult') and name='nCompTimeOffHoursAuditing')
begin
alter table Hr_tm_MonthResult add nCompTimeOffHoursAuditing [numeric](7,2) null
end
--月结果表--
--加班单行抵扣子表--
--hr_tm_overtimeResultDeduct(加班单行抵扣子表)新增 nDeductOverTimeAuditing(抵扣加班时间(审核)),可为空,Number(6,2)
if not exists
(select *from syscolumns where id=OBJECT_ID('hr_tm_overtimeResultDeduct') and name='nDeductOverTimeAuditing')
begin
alter table hr_tm_overtimeResultDeduct add nDeductOverTimeAuditing [numeric](7,2) null
end
--hr_tm_overtimeResultDeduct(加班单行抵扣子表)新增 nDeductOverTimeAudited 抵扣加班时间(已审核) 可为空,Number(7,2)
if not exists
(select *from syscolumns where id=OBJECT_ID('hr_tm_overtimeResultDeduct') and name='nDeductOverTimeAudited')
begin
alter table hr_tm_overtimeResultDeduct add nDeductOverTimeAudited [numeric](7,2) null
end
--hr_tm_overtimeResultDeduct(加班单行抵扣子表)新增 nDealTimeAuditing 抵扣请假时间(审核中) 可为空,Number(7,2)
if not exists
(select *from syscolumns where id=OBJECT_ID('hr_tm_overtimeResultDeduct') and name='nDealTimeAuditing')
begin
alter table hr_tm_overtimeResultDeduct add nDealTimeAuditing [numeric](7,2) null
end
--hr_tm_overtimeResultDeduct(加班单行抵扣子表)新增 nDealTimeAudited 抵扣请假时间(已审核) 可为空,Number(7,2)
if not exists
(select *from syscolumns where id=OBJECT_ID('hr_tm_overtimeResultDeduct') and name='nDealTimeAudited')
begin
alter table hr_tm_overtimeResultDeduct add nDealTimeAudited [numeric](7,2) null
end
--加班单行抵扣子表--
--删除列的默认值
IF EXISTS ( SELECT name
FROM sysobjects
WHERE id = ( SELECT syscolumns.cdefault
FROM sysobjects
INNER JOIN syscolumns ON sysobjects.Id = syscolumns.Id
WHERE sysobjects.name = N'HR_TM_OverTimeVoucher'
AND syscolumns.name = N'dAuditTime'
) )
BEGIN
EXECUTE sp_unbindefault N'dbo.HR_TM_OverTimeVoucher.dAuditTime'
END
效率测试报告:开始升级UFDATA_002_2015数据库
Data_STR_PB_mix_NL.SQL,2020-04-05 23:08:04 -- 2020-04-05 23:08:04,0小时0分钟0秒。
Data_STR_PB_Vou_NL.SQL,2020-04-05 23:08:04 -- 2020-04-05 23:08:05,0小时0分钟1秒。
DATA_STR_WF_MIX_NL.SQL,2020-04-05 23:08:05 -- 2020-04-05 23:08:05,0小时0分钟0秒。
data_str_pb_wf_nl.SQL,2020-04-05 23:08:05 -- 2020-04-05 23:08:05,0小时0分钟0秒。
data_str_ss_mix_nl.SQL,2020-04-05 23:08:05 -- 2020-04-05 23:08:05,0小时0分钟0秒。
data_str_tm_mix_nl__up.SQL,2020-04-05 23:08:05 -- 2020-04-05 23:08:05,0小时0分钟0秒。
升级起始时间:2020-04-05 23:08:04,结束时间:2020-04-05 23:08:05,0小时0分钟1秒。