江苏金软软件技术有限公司

一、固定资产结账报错，提示数据转换错误：
问题描述：固定资产系统结账时，系统提示：“将expression转换为数据类型int时发生算术溢出错误” 
问题原因：固定资产系统中保存的每期卡片内码值太大，造成内码值超过字段定义的数据类型的最大值 
解决方法：处理：需要删除多余数据，并重建卡片内码 
go 
declare @fyear int 
declare @period int 
select * from t_systemprofile where fcategory='fa' and fkey='currentyear'
select * from t_systemprofile where fcategory='fa' and 
fkey='CurrentPeriod' 
select @fyear=fvalue from t_systemprofile where fcategory='fa' and 
fkey='currentyear' 
select @period=fvalue from t_systemprofile where fcategory='fa' and 
fkey='CurrentPeriod' 
1、创建临时数据表 
if exists (select * from sysobjects where name='t_fabal_hetemp_2') 
begin 
drop table t_fabal_hetemp_2 
end 
select * into t_fabal_hetemp_2  
from t_fabalance  
where  (fyear*100+fperiod)>(@fyear*100+@period) 
and fassetid not in (select fassetid from t_faalter where 
(fyear*100+fperiod)>(@fyear*100+@period) ) 
select * from t_fabal_hetemp_2 
2、根据临时表删除相关数据
delete from  t_FABalCardItem  where fbalid in (select fbalid from  t_fabal_hetemp_2 ) 
delete from  t_FABalCard where fbalid in (select fbalid from  t_fabal_hetemp_2 ) 
delete from  t_FABaldevice where fbalid in (select fbalid from  t_fabal_hetemp_2 ) 
delete from  t_FABalorgfor where fbalid in (select fbalid from  t_fabal_hetemp_2 ) 
delete from  t_FABaldept where fbalid in (select fbalid from  t_fabal_hetemp_2 ) 
delete from  t_FABalexpense where fbalid in (select fbalid from  t_fabal_hetemp_2 ) 
delete from  t_FABalPurchase where fbalid in (select fbalid from  t_fabal_hetemp_2 ) 
delete from t_fabalance where fbalid in (select fbalid from  t_fabal_hetemp_2 ) 
drop table t_fabal_hetemp_2  
3、重建卡片内码 
3.1更新之前，禁用约束 
ALTER TABLE t_fabalcarditem NOCHECK CONSTRAINT 
FK_t_FABalCardItem_t_FABalance 
ALTER TABLE t_fabalcard NOCHECK CONSTRAINT FK_t_FABalCard_t_FABalance 
ALTER TABLE t_fabaldevice NOCHECK CONSTRAINT FK_t_FABaldevice_t_FABalance
ALTER TABLE t_fabalorgfor NOCHECK CONSTRAINT FK_t_FABalorgfor_t_FABalance
ALTER TABLE t_fabaldept NOCHECK CONSTRAINT FK_t_FABaldept_t_FABalance 
ALTER TABLE t_fabalexpense NOCHECK CONSTRAINT 
FK_t_FABalexpense_t_FABalance 
ALTER TABLE t_FABalPurchase NOCHECK CONSTRAINT 
FK_t_FABalPurchase_t_FABalance 
3.2生成新旧内码对照表 
select distinct(fbalid) AS Foldid,IDENTITY(int,1,1) as 
fnewid,fyear,fperiod into #tmpa from t_fabalance 
3.3用新内码更新就内码 
update t_fabalcarditem set fbalid=#tmpa.fnewid from  t_fabalcarditem  carditem,#tmpa where #tmpa.foldid=carditem.fbalid 
update t_fabalcard set fbalid=#tmpa.fnewid from  t_fabalcard  carditem,#tmpa where #tmpa.foldid=carditem.fbalid 
update t_fabaldevice set fbalid=#tmpa.fnewid from  t_fabaldevice  carditem,#tmpa where #tmpa.foldid=carditem.fbalid 
update t_fabalorgfor set fbalid=#tmpa.fnewid from  t_fabalorgfor  carditem,#tmpa where #tmpa.foldid=carditem.fbalid 
update t_fabaldept set fbalid=#tmpa.fnewid from  t_fabaldept  carditem,#tmpa where #tmpa.foldid=carditem.fbalid 
update t_fabalexpense set fbalid=#tmpa.fnewid from  t_fabalexpense  carditem,#tmpa where #tmpa.foldid=carditem.fbalid 
update t_FABalPurchase set fbalid=#tmpa.fnewid from  t_FABalPurchase  carditem,#tmpa where #tmpa.foldid=carditem.fbalid 
update t_fabalance set fbalid=#tmpa.fnewid from  t_fabalance  carditem,#tmpa where #tmpa.foldid=carditem.fbalid 
update t_identity set fnext=(select max(fnewid)+1 from #tmpa) where  fname='t_fabalance' 
select * from #tmpa order by fyear,fperiod 
drop table #tmpa 
更新完毕，启用约束 
ALTER TABLE t_fabalcarditem CHECK CONSTRAINT 
FK_t_FABalCardItem_t_FABalance 
ALTER TABLE t_fabalcard CHECK CONSTRAINT FK_t_FABalCard_t_FABalance 
ALTER TABLE t_fabaldevice CHECK CONSTRAINT FK_t_FABaldevice_t_FABalance
ALTER TABLE t_fabalorgfor CHECK CONSTRAINT FK_t_FABalorgfor_t_FABalance
ALTER TABLE t_fabaldept CHECK CONSTRAINT FK_t_FABaldept_t_FABalance 
ALTER TABLE t_fabalexpense CHECK CONSTRAINT FK_t_FABalexpense_t_FABalance
ALTER TABLE t_FABalPurchase CHECK CONSTRAINT 
FK_t_FABalPurchase_t_FABalance 
完毕 
更新完毕后，建议对于已经存当前期间以后的卡片业务删除后进行重做。 
go 
南通金蝶/南通金蝶软件-南通金软软件