|
马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。
您需要 登录 才可以下载或查看,没有账号?注册账号
×
U8从8.9升级到10.0,2015年和2016年升级没有问题,但是2017年和2018年升级就会报错数据库名: UFDATA_007_2018
C:\U8SOFT\Admin\SQLFILE10000\Main\Ufdata\Structure\data_str_wa_mix_nl.sql
错误信息:
999
执行该sql语句出错,导致事务回滚
执行如下语句时出错:
----初始化年度字段
if COLUMNPROPERTY(OBJECT_ID('hr_wa_importitem'),'iyear','PRECISION') IS not NULL
begin
if exists(select * from WA_account)
begin
update hr_wa_importitem set iyear = (select max(ilastyear) from WA_account) where iyear is null
end
else
begin
delete from hr_wa_importitem where iYear is null
end
ALTER TABLE hr_wa_importitem ALTER COLUMN iyear smallint NOT NULL
end
if COLUMNPROPERTY(OBJECT_ID('WA_TaxArea'),'iyear','PRECISION') IS not NULL
begin
update WA_TaxArea set iyear = (select max(ilastyear) from WA_account) where iyear is null
ALTER TABLE WA_TaxArea ALTER COLUMN iyear smallint NOT NULL
end
if COLUMNPROPERTY(OBJECT_ID('WA_TaxBB'),'iyear','PRECISION') IS not NULL
begin
update WA_TaxBB set iyear = (select max(ilastyear) from WA_account) where iyear is null
ALTER TABLE WA_TaxBB ALTER COLUMN iyear smallint NOT NULL
end
if COLUMNPROPERTY(OBJECT_ID('WA_TaxFormat'),'iyear','PRECISION') IS not NULL
begin
update WA_TaxFormat set iyear = (select isnull(max(ilastyear),year(GETDATE())) from WA_account) where iyear is null
ALTER TABLE WA_TaxFormat ALTER COLUMN iyear smallint NOT NULL
end
if COLUMNPROPERTY(OBJECT_ID('WA_TaxMailSet'),'iyear','PRECISION') IS not NULL
begin
update WA_TaxMailSet set iyear = (select max(ilastyear) from WA_account) where iyear is null
ALTER TABLE WA_TaxMailSet ALTER COLUMN iyear smallint NOT NULL
end
if COLUMNPROPERTY(OBJECT_ID('wa_fforder'),'iyear','PRECISION') IS not NULL
begin
update wa_fforder set iyear = (select max(ilastyear) from WA_account) where iyear is null
ALTER TABLE wa_fforder ALTER COLUMN iyear smallint NOT NULL
end
if COLUMNPROPERTY(OBJECT_ID('wa_gzdl'),'iyear','PRECISION') IS not NULL
begin
update wa_gzdl set iyear = (select max(ilastyear) from WA_account) where iyear is null
ALTER TABLE wa_gzdl ALTER COLUMN iyear smallint NOT NULL
end
if COLUMNPROPERTY(OBJECT_ID('wa_outperiod'),'iyear','PRECISION') IS not NULL
begin
update wa_outperiod set iyear = (select max(ilastyear) from WA_account) where iyear is null
ALTER TABLE wa_outperiod ALTER COLUMN iyear smallint NOT NULL
end
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'PK_WA_OutPeriod') )
BEGIN
ALTER TABLE wa_outperiod
DROP CONSTRAINT PK_WA_OutPeriod
END
ALTER TABLE [dbo].[wa_outperiod] ADD
CONSTRAINT [PK_WA_OutPeriod] PRIMARY KEY NONCLUSTERED
(
[cGZGradeNum],
[iYear],
[iPeriod]
) ON [PRIMARY]
if COLUMNPROPERTY(OBJECT_ID('WA_GZItem'),'iyear','PRECISION') IS not NULL
begin
update WA_GZItem set iyear = (select max(ilastyear) from WA_account) where iyear is null
ALTER TABLE WA_GZItem ALTER COLUMN iyear smallint NOT NULL
end
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'aaaaaWA_GZItem_PK') )
BEGIN
ALTER TABLE WA_GZItem
DROP CONSTRAINT aaaaaWA_GZItem_PK
END
ALTER TABLE [dbo].[WA_GZItem] ADD
CONSTRAINT [aaaaaWA_GZItem_PK] PRIMARY KEY NONCLUSTERED
(
[cGZGradeNum],
[iYear],
[iOrder]
) ON [PRIMARY]
if COLUMNPROPERTY(OBJECT_ID('WA_Formula'),'iyear','PRECISION') IS not NULL
begin
update WA_Formula set iyear = (select max(ilastyear) from WA_account) where iyear is null
ALTER TABLE WA_Formula ALTER COLUMN iyear smallint NOT NULL
end
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'aaaaaWA_GZItem_PK') )
BEGIN
ALTER TABLE WA_Formula
DROP CONSTRAINT aaaaaWA_formula_PK
END
ALTER TABLE [dbo].[WA_Formula] ADD
CONSTRAINT [aaaaaWA_formula_PK] PRIMARY KEY NONCLUSTERED
(
[cGZGradeNum],
[iYear],
[iFormulaNum]
) ON [PRIMARY]
if COLUMNPROPERTY(OBJECT_ID('WA_Bank_Set'),'iyear','PRECISION') IS not NULL
begin
update WA_Bank_Set set iyear = (select max(ilastyear) from WA_account) where iyear is null
ALTER TABLE WA_Bank_Set ALTER COLUMN iyear smallint NOT NULL
end
--if COLUMNPROPERTY(OBJECT_ID('WA_Auth'),'iyear','PRECISION') IS not NULL
--begin
-- update WA_Auth set iyear = (select max(ilastyear) from WA_account) where iyear is null
-- ALTER TABLE WA_Auth ALTER COLUMN iyear smallint NOT NULL
--end
--IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'WA_Auth_PK') )
--BEGIN
--ALTER TABLE WA_Auth
--DROP CONSTRAINT WA_Auth_PK
--END
--ALTER TABLE [dbo].[WA_Auth] ADD
-- CONSTRAINT [WA_Auth_PK] PRIMARY KEY NONCLUSTERED
-- (
-- [cGZGradeNum],
-- [iYear],
-- [Iuserid]
-- ) ON [PRIMARY]
-------
--if COLUMNPROPERTY(OBJECT_ID('WA_Authority'),'iyear','PRECISION') IS not NULL
--begin
-- update WA_Authority set iyear = (select max(ilastyear) from WA_account) where iyear is null
-- ALTER TABLE WA_Authority ALTER COLUMN iyear smallint NOT NULL
--end
if COLUMNPROPERTY(OBJECT_ID('wa_state'),'iyear','PRECISION') IS not NULL
begin
update wa_state set iyear = (select max(ilastyear) from WA_account) where iyear is null
ALTER TABLE wa_state ALTER COLUMN iyear smallint NOT NULL
end
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'aaaaaWA_State_PK') )
BEGIN
ALTER TABLE wa_state
DROP CONSTRAINT aaaaaWA_State_PK
END
ALTER TABLE [dbo].[wa_state] ADD
CONSTRAINT [aaaaaWA_State_PK] PRIMARY KEY NONCLUSTERED
(
[cGZGradeNum],
[iYear],
[iMonth]
) ON [PRIMARY]
-----
ALTER TABLE wa_account ALTER COLUMN iLastYear smallint NOT NULL
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'aaaaaWA_account_PK') )
BEGIN
ALTER TABLE wa_account
DROP CONSTRAINT aaaaaWA_account_PK
END
ALTER TABLE [dbo].[wa_account] ADD
CONSTRAINT [aaaaaWA_account_PK] PRIMARY KEY NONCLUSTERED
(
[cGZGradeNum],
[iLastYear]
) ON [PRIMARY]
-----
if COLUMNPROPERTY(OBJECT_ID('wa_dept'),'iyear','PRECISION') IS not NULL
begin
update wa_dept set iyear = (select max(ilastyear) from WA_account) where iyear is null
ALTER TABLE wa_dept ALTER COLUMN iyear smallint NOT NULL
end
ALTER TABLE wa_dept ALTER COLUMN iYear smallint NOT NULL
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'aaaaaWA_dept_PK') )
BEGIN
ALTER TABLE wa_dept
DROP CONSTRAINT aaaaaWA_dept_PK
END
ALTER TABLE [dbo].[wa_dept] ADD
CONSTRAINT [aaaaaWA_dept_PK] PRIMARY KEY NONCLUSTERED
(
[cGZGradeNum],
[iYear],
[cDept_Num]
) ON [PRIMARY]
if COLUMNPROPERTY(OBJECT_ID('wa_psn'),'iyear','PRECISION') IS not NULL
begin
update wa_psn set iyear = (select max(ilastyear) from WA_account) where iyear is null
ALTER TABLE wa_psn ALTER COLUMN iyear smallint NOT NULL
end
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'aaaaaWA_psn_PK') )
BEGIN
ALTER TABLE wa_psn
DROP CONSTRAINT aaaaaWA_psn_PK
END
ALTER TABLE [dbo].[wa_psn] ADD
CONSTRAINT [aaaaaWA_psn_PK] PRIMARY KEY NONCLUSTERED
(
[cGZGradeNum],
[iYear],
[cPsn_Num],
[cPsn_Name]
) ON [PRIMARY]
--基数
if COLUMNPROPERTY(OBJECT_ID('WA_SDS_p'),'iyear','PRECISION') IS not NULL
begin
update WA_SDS_p set iyear = (select max(ilastyear) from WA_account) where iyear is null
end
--代付税
if COLUMNPROPERTY(OBJECT_ID('WA_SDS_SLNew'),'iyear','PRECISION') IS not NULL
begin
update WA_SDS_SLNew set iyear = (select max(ilastyear) from WA_account) where iyear is null
end
--代扣税
if COLUMNPROPERTY(OBJECT_ID('WA_SDS_SL'),'iyear','PRECISION') IS not NULL
begin
update WA_SDS_SL set iyear = (select max(ilastyear) from WA_account) where iyear is null
end
--汇率
if COLUMNPROPERTY(OBJECT_ID('WA_currency'),'iyear','PRECISION') IS not NULL
begin
update WA_currency set iyear = (select max(ilastyear) from WA_account) where iyear is null
end
----工资分摊详细设置
if COLUMNPROPERTY(OBJECT_ID('WA_GZsumComCfg'),'iYear','PRECISION') IS not NULL
begin
update WA_GZsumComCfg set iyear = (select max(ilastyear) from WA_account) where iyear is null
end
----工资分摊名称
if COLUMNPROPERTY(OBJECT_ID('WA_FTName'),'iYear','PRECISION') IS not NULL
begin
update WA_FTName set iyear = (select max(ilastyear) from WA_account) where iyear is null
ALTER TABLE WA_FTName ALTER COLUMN iyear smallint NOT NULL
end
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'aaaaaWA_FTName_PK') )
BEGIN
ALTER TABLE WA_FTName
DROP CONSTRAINT aaaaaWA_FTName_PK
END
ALTER TABLE [dbo].[WA_FTName] ADD
CONSTRAINT [aaaaaWA_FTName_PK] PRIMARY KEY NONCLUSTERED
(
[cGZGradeNum],
[iYear],
[iJT_id]
) ON [PRIMARY]
---个人所得税表
if COLUMNPROPERTY(OBJECT_ID('WA_SDS_Ex'),'iYear','PRECISION') IS not NULL
begin
update WA_SDS_Ex set iyear = (select max(ilastyear) from WA_account) where iyear is null
ALTER TABLE WA_SDS_Ex ALTER COLUMN iyear smallint NOT NULL
end
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'PK_WA_SDS_Ex') )
BEGIN
ALTER TABLE WA_SDS_Ex
DROP CONSTRAINT PK_WA_SDS_Ex
END
ALTER TABLE [dbo].[WA_SDS_Ex] ADD
CONSTRAINT [PK_WA_SDS_Ex] PRIMARY KEY NONCLUSTERED
(
[cGZGradeNum],
[iYear],
[iPeriod],
[cPsn_Num],
[iOutPeriod]
) ON [PRIMARY]
---报表需要添加年度字段
--修改821客户账套升级失败的问题,没有删除干净主外键
declare @str nvarchar(4000)
set @str=''
select @str=@str+'ALTER TABLE ['+object_name(fkeyid)+'] DROP CONSTRAINT ['+ object_name(constid)+'] '
from sysforeignkeys where rkeyid=object_id('WA_GZBName') group by constid,fkeyid,rkeyid
if len(@str) > 0
begin
exec (@str)
end
---结束
if COLUMNPROPERTY(OBJECT_ID('WA_GZBName'),'iYear','PRECISION') IS not NULL
begin
update WA_GZBName set iyear = (select max(ilastyear) from WA_account) where iyear is null
ALTER TABLE WA_GZBName ALTER COLUMN iyear smallint NOT NULL
end
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'FK__WA_GZBItemGrd__6DE226F3') )
BEGIN
ALTER TABLE WA_GZBItemGrd
DROP CONSTRAINT FK__WA_GZBItemGrd__6DE226F3
END
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'FK__WA_GZBItemGrd__6ED64B2C') )
BEGIN
ALTER TABLE WA_GZBItemGrd
DROP CONSTRAINT FK__WA_GZBItemGrd__6ED64B2C
END
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'FK__WA_GZBItemGrd__7A47FDD8') )
BEGIN
ALTER TABLE WA_GZBItemGrd
DROP CONSTRAINT FK__WA_GZBItemGrd__7A47FDD8
END
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'FK__WA_GZBItemTitle__6ED64B2C') )
BEGIN
ALTER TABLE WA_GZBItemTitle
DROP CONSTRAINT FK__WA_GZBItemTitle__6ED64B2C
END
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'FK__WA_GZBItemTitle__6FCA6F65') )
BEGIN
ALTER TABLE WA_GZBItemTitle
DROP CONSTRAINT FK__WA_GZBItemTitle__6FCA6F65
END
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'FK__WA_GZBItemTitle__7B3C2211') )
BEGIN
ALTER TABLE WA_GZBItemTitle
DROP CONSTRAINT FK__WA_GZBItemTitle__7B3C2211
END
--IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'FK__WA_GZFXBItemTitl__275079D0') )
--BEGIN
--ALTER TABLE WA_GZFXBItemTitle
--DROP CONSTRAINT FK__WA_GZFXBItemTitl__275079D0
--END
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[WA_GZFXBItemTitle]'))
begin
drop table WA_GZFXBItemTitle
end
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'aaaaaWA_GZBName_PK') )
BEGIN
ALTER TABLE WA_GZBName
DROP CONSTRAINT aaaaaWA_GZBName_PK
END
ALTER TABLE [dbo].[WA_GZBName] ADD
CONSTRAINT [aaaaaWA_GZBName_PK] PRIMARY KEY NONCLUSTERED
(
[cGZGradeNum],
[iYear],
[iGZBName_id]
) ON [PRIMARY]
if COLUMNPROPERTY(OBJECT_ID('WA_GZBItemDept'),'iYear','PRECISION') IS not NULL
begin
update WA_GZBItemDept set iyear = (select max(ilastyear) from WA_account) where iyear is null
end
if COLUMNPROPERTY(OBJECT_ID('WA_GZBItemGrd'),'iYear','PRECISION') IS not NULL
begin
update WA_GZBItemGrd set iyear = (select max(ilastyear) from WA_account) where iyear is null
end
if COLUMNPROPERTY(OBJECT_ID('WA_GZBItemTitle'),'iYear','PRECISION') IS not NULL
begin
update WA_GZBItemTitle set iyear = (select max(ilastyear) from WA_account) where iyear is null
ALTER TABLE WA_GZBItemTitle ALTER COLUMN iyear smallint NOT NULL
end
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'PK_WA_GZBItemTitle') )
BEGIN
ALTER TABLE WA_GZBItemTitle
DROP CONSTRAINT PK_WA_GZBItemTitle
END
ALTER TABLE [dbo].[WA_GZBItemTitle] ADD
CONSTRAINT [PK_WA_GZBItemTitle] PRIMARY KEY NONCLUSTERED
(
[cGZGradeNum],
[iYear],
[iGZBName_id],
[cGZItemTitle]
) ON [PRIMARY]
ALTER TABLE WA_GZBItemTitle
ADD CONSTRAINT FK__WA_GZBItemTitle__6ED64B2C FOREIGN KEY (cGZGradeNum,iYear,iGZBName_id)
REFERENCES WA_GZBName (cGZGradeNum,iYear,iGZBName_id) ;
ALTER TABLE WA_GZBItemGrd
ADD CONSTRAINT FK__WA_GZBItemGrd__6DE226F3 FOREIGN KEY (cGZGradeNum,iYear,iGZBName_id)
REFERENCES WA_GZBName (cGZGradeNum,iYear,iGZBName_id) ;
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'WA_TaxBB_PK') )
BEGIN
ALTER TABLE WA_TaxBB
DROP CONSTRAINT WA_TaxBB_PK
END
ALTER TABLE [dbo].[WA_TaxBB] ADD
CONSTRAINT [WA_TaxBB_PK] PRIMARY KEY NONCLUSTERED
(
[cGZGradeNum],
[iYear],
[Type_Num],
[TaxBB_Name],
[Tax_Area]
) ON [PRIMARY]
----结束报表需要添加年度字段
---分段计薪需要添加年度字段
if COLUMNPROPERTY(OBJECT_ID('WA_GZSectionItem'),'iYear','PRECISION') IS not NULL
begin
update WA_GZSectionItem set iyear = (select max(ilastyear) from WA_account) where iyear is null
ALTER TABLE WA_GZSectionItem ALTER COLUMN iyear smallint NOT NULL
end
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'PK_WA_GZSectionItem') )
BEGIN
ALTER TABLE WA_GZSectionItem
DROP CONSTRAINT PK_WA_GZSectionItem
END
ALTER TABLE [dbo].[WA_GZSectionItem] ADD
CONSTRAINT [PK_WA_GZSectionItem] PRIMARY KEY NONCLUSTERED
(
[cGZGradeNum],
[iYear],
[iGZItem_id]
) ON [PRIMARY]
if COLUMNPROPERTY(OBJECT_ID('WA_Sectionformula'),'iYear','PRECISION') IS not NULL
begin
update WA_Sectionformula set iyear = (select max(ilastyear) from WA_account) where iyear is null
ALTER TABLE WA_Sectionformula ALTER COLUMN iyear smallint NOT NULL
end
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'PK_WA_Sectionformula') )
BEGIN
ALTER TABLE WA_Sectionformula
DROP CONSTRAINT PK_WA_Sectionformula
END
ALTER TABLE [dbo].[WA_Sectionformula] ADD
CONSTRAINT [PK_WA_Sectionformula] PRIMARY KEY NONCLUSTERED
(
[cGZGradeNum],
[iYear],
[iGZItem_id]
) ON [PRIMARY]
效率测试报告:开始升级UFDATA_007_2018数据库
data_str_pb_wfmodel_nl.SQL,2018-10-11 14:08:38 -- 2018-10-11 14:08:53,0小时0分钟15秒。
data_str_pb_mommodel_nl.SQL,2018-10-11 14:08:53 -- 2018-10-11 14:08:58,0小时0分钟5秒。
data_str_pb_portalmodel_nl.SQL,2018-10-11 14:08:58 -- 2018-10-11 14:09:10,0小时0分钟12秒。
data_str_pb_ufsubmodel_nl.SQL,2018-10-11 14:09:10 -- 2018-10-11 14:09:11,0小时0分钟1秒。
Data_STR_PB_DR_NL.SQL,2018-10-11 14:09:11 -- 2018-10-11 14:09:13,0小时0分钟2秒。
Data_STR_PB_mix_NL.SQL,2018-10-11 14:09:13 -- 2018-10-11 14:09:42,0小时0分钟29秒。
Data_STR_PB_Vou_NL.SQL,2018-10-11 14:09:42 -- 2018-10-11 14:09:43,0小时0分钟1秒。
data_str_pb_uap_nl.SQL,2018-10-11 14:09:43 -- 2018-10-11 14:09:43,0小时0分钟0秒。
data_str_pb_as_nl.SQL,2018-10-11 14:09:43 -- 2018-10-11 14:09:48,0小时0分钟5秒。
data_str_pb_start_nl.SQL,2018-10-11 14:09:48 -- 2018-10-11 14:09:49,0小时0分钟1秒。
data_str_pb_dae_nl.SQL,2018-10-11 14:09:49 -- 2018-10-11 14:09:49,0小时0分钟0秒。
workflow_str_pb_mix_nl.SQL,2018-10-11 14:09:49 -- 2018-10-11 14:09:49,0小时0分钟0秒。
Data_STR_SR_mix_NL.SQL,2018-10-11 14:09:49 -- 2018-10-11 14:09:50,0小时0分钟1秒。
data_str_rt_mix_nl.SQL,2018-10-11 14:09:50 -- 2018-10-11 14:09:51,0小时0分钟1秒。
data_str_ss_mix_nl.SQL,2018-10-11 14:09:51 -- 2018-10-11 14:09:51,0小时0分钟0秒。
升级起始时间:2018-10-11 14:08:35,结束时间:2018-10-11 14:09:55,0小时1分钟20秒。
求助怎么解决!!!
|
|