技术区 › 悬赏问答区 › U852升级至U890时提示：升级失败! 高手给解决啊!

10010 发表于 2010-3-18 17:50:38

U852升级至U890时提示：升级失败! 高手给解决啊!

数据库名: UFDATA_001_1999
D:\U8SOFT\Admin\SQLFILE850\Ufdata\DPRD_Public.sql
错误信息:
-2147217873
无法将 NULL 值插入列 'VT_ID'，表 'UFDATA_001_1999.dbo.VoucherItems'；该列不允许空值。UPDATE 失败。
执行如下语句时出错:
--升级所有的单据模版
if exists (select * from dbo.sysobjects where id = object_id(N'.') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
declare @TblCount smallint
declare @TblName varchar(30)
declare @iNoOld_VT varchar(20)
declare @iNoNew_VT varchar(20)
set @TblName = 'InvoiceItem_SAl'
set @TblCount=1
while (@TblCount<=7)
   begin
----升级显示格式模版
exec ('Insert Into VoucherItems(VT_ID,CardNum,CardItemNum,CardItemName,CanNotSelect,
         ShowIt,LinkIt,LinkTbl,tablename,fieldname,fieldtype,
         refertype,refertable,cardsection,cardformula1,
         cardformula2,CanModify,MaxLength,MaxShowLen,NumPoint,
         CanDelete,UserCheck,UserPrompt,FormatChar,IsMain,
         NeedSum,CalcField,LinkField,AliasNum,isSelfdef,cox,coy,tabindex,Height,Width)
Select -10000,CardNum,CardItemNum,CardItemName,CanNotSelect,
         ShowIt,LinkIt,LinkTbl,tablename,fieldname,fieldtype,
         refertype,refertable,cardsection,cardformula1,
         cardformula2,CanModify,MaxLength,MaxShowLen,NumPoint,
         CanDelete,UserCheck,UserPrompt,FormatChar,IsMain,
         NeedSum,CalcField,LinkField,AliasNum,isSelfdef,cox-datalength(CardItemName)*90,coy,tabidx,275,maxshowlen+datalength(CardItemName)*90
From ' + @TblName)
----升级打印格式模版
exec ('Insert Into VoucherItems_Prn(VT_ID,CardNum,CardItemNum,CardItemName,CanNotSelect,
         ShowIt,LinkIt,LinkTbl,tablename,fieldname,fieldtype,
         refertype,refertable,cardsection,cardformula1,
         cardformula2,CanModify,MaxLength,MaxShowLen,NumPoint,
         CanDelete,UserCheck,UserPrompt,FormatChar,IsMain,
         NeedSum,CalcField,LinkField,AliasNum,isSelfdef,cox,coy,tabindex,Height,Width)
Select -10000,CardNum,CardItemNum,CardItemName,CanNotSelect,
         ShowIt,LinkIt,LinkTbl,tablename,fieldname,fieldtype,
         refertype,refertable,cardsection,cardformula1,
         cardformula2,CanModify,MaxLength,MaxShowLen,NumPoint,
         CanDelete,UserCheck,UserPrompt,FormatChar,IsMain,
         NeedSum,CalcField,LinkField,AliasNum,isSelfdef,cox-datalength(CardItemName)*90,coy,tabidx,275,maxshowlen+datalength(CardItemName)*90
From ' + @TblName)

set @iNoNew_VT=1
declare Cur_VoucherTemplates cursor for SELECT CardNum
    FROM VoucherItems where VT_ID=-10000
OPEN Cur_VoucherTemplates
FETCH next FROM Cur_VoucherTemplates INTO @iNoOld_VT
WHILE @@FETCH_STATUS = 0
BEGIN
set @iNoNew_VT=(select VT_ID From VoucherTemplates
Where VoucherTemplates.VT_CardNumber = @iNoOld_VT and
VoucherTemplates.VT_TemplateMode=0)
----更新显示模版
update VoucherItems Set VT_ID =@iNoNew_VT Where CardNum=@iNoOld_VT
                update Vouchers Set DEF_ID =@iNoNew_VT Where CardNumber=@iNoOld_VT
----更新打印模版
set @iNoNew_VT=(select VT_ID From VoucherTemplates
Where VoucherTemplates.VT_CardNumber = @iNoOld_VT and
VoucherTemplates.VT_TemplateMode=1)
update VoucherItems_Prn Set VT_ID =@iNoNew_VT Where CardNum=@iNoOld_VT
                update Vouchers Set DEF_ID_PRN =@iNoNew_VT Where CardNumber=@iNoOld_VT
FETCH NEXT FROM Cur_VoucherTemplates INTO @iNoOld_VT
end
CLOSE Cur_VoucherTemplates
DEALLOCATE Cur_VoucherTemplates
      set @tblCount=@tblCount+1
if (@tblCount=2)
   set @TblName = 'InvoiceItem_Ap'
else
   if (@tblCount=3)
set @TblName = 'InvoiceItema'
   else
if (@tblCount=4)
      set @TblName = 'InvoiceItem_'
else
      if (@tblCount=5)
          set @TblName = 'KCHSItem'
      else
   if (@tblCount=6)
       set @TblName = 'PO'
   else
      if (@tblCount=7)
         set @TblName = 'PS'
exec ('delete ' + @TblName + ' where CardNum=' + '''01''')
   end
   ----调整最大长度
   update VoucherItems Set MaxLength =50 Where tablename='Bank' and fieldname='cBAccount'
   update VoucherItems Set MaxLength =100 Where tablename='Bank' and fieldname='cBName'
   update VoucherItems Set MaxLength =200 Where tablename='Customer' and fieldname='cCusOAddress'
   update VoucherItems Set MaxLength =200 Where tablename='Customer' and fieldname='cCusAddress'
   update VoucherItems Set MaxLength =100 Where tablename='Customer' and fieldname='cCusBank'
   update VoucherItems Set MaxLength =100 Where tablename='Customer' and fieldname='cCusPhone'
   update VoucherItems Set MaxLength =30 Where tablename='Inventory' and fieldname='cInvAddCode'
   update VoucherItems Set MaxLength =60 Where tablename='Inventory' and fieldname='cInvStd'
   update VoucherItems Set MaxLength =20 Where tablename='Person' and fieldname='cPersonName'
   update VoucherItems Set MaxLength =200 Where tablename='Vendor' and fieldname='cVenIAddress'
   update VoucherItems Set MaxLength =20 Where tablename='Vendor' and fieldname='cVenCode'
   update VoucherItems Set MaxLength =200 Where tablename='Vendor' and fieldname='cVenAddress'
   update VoucherItems Set MaxLength =100 Where tablename='Vendor' and fieldname='cVenBank'
   update VoucherItems Set MaxLength =100 Where tablename='Vendor' and fieldname='cVenPhone'
   update VoucherItems Set MaxLength =50 Where tablename='Vendor' and fieldname='cVenAccount'
   
   update VoucherItems_Prn Set MaxLength =50 Where tablename='Bank' and fieldname='cBAccount'
   update VoucherItems_Prn Set MaxLength =100 Where tablename='Bank' and fieldname='cBName'
   update VoucherItems_Prn Set MaxLength =200 Where tablename='Customer' and fieldname='cCusOAddress'
   update VoucherItems_Prn Set MaxLength =200 Where tablename='Customer' and fieldname='cCusAddress'
   update VoucherItems_Prn Set MaxLength =100 Where tablename='Customer' and fieldname='cCusBank'
   update VoucherItems_Prn Set MaxLength =100 Where tablename='Customer' and fieldname='cCusPhone'
   update VoucherItems_Prn Set MaxLength =30 Where tablename='Inventory' and fieldname='cInvAddCode'
   update VoucherItems_Prn Set MaxLength =60 Where tablename='Inventory' and fieldname='cInvStd'
   update VoucherItems_Prn Set MaxLength =20 Where tablename='Person' and fieldname='cPersonName'
   update VoucherItems_Prn Set MaxLength =200 Where tablename='Vendor' and fieldname='cVenIAddress'
   update VoucherItems_Prn Set MaxLength =20 Where tablename='Vendor' and fieldname='cVenCode'
   update VoucherItems_Prn Set MaxLength =200 Where tablename='Vendor' and fieldname='cVenAddress'
   update VoucherItems_Prn Set MaxLength =100 Where tablename='Vendor' and fieldname='cVenBank'
   update VoucherItems_Prn Set MaxLength =100 Where tablename='Vendor' and fieldname='cVenPhone'
   update VoucherItems_Prn Set MaxLength =50 Where tablename='Vendor' and fieldname='cVenAccount'
   ----删除旧的格式表
   drop table InvoiceItem_SAl
   drop table InvoiceItem_Ap
   drop table InvoiceItem_
   drop table InvoiceItema
   drop table KCHSItem
   drop table Po
   drop table Ps
   drop table InvoiceItem_Prn
   drop table InvoiceItem_SalPrn
   drop table InvoiceItemaPrn
   drop table KCHSItemPrn
   drop table InvoiceItem
end

效率测试报告：开始升级UFDATA_001_1999数据库
Update_DelRepeat.sql，2010-3-18 9:52:39 -- 2010-3-18 9:52:39，0小时0分钟0秒。
DSTR_Public.SQL，2010-3-18 9:52:39 -- 2010-3-18 9:52:41，0小时0分钟2秒。
    升级起始时间：2010-3-18 9:52:26，结束时间：2010-3-18 9:52:49，0小时0分钟23秒。

bei837613235 发表于 2010-3-20 10:55:14

做为一个一般人，面对这个问题，只能说：看来还是得努力提升业务水平啊！

shankaifeng 发表于 2010-3-20 16:15:46

这个问题让sql高人去弄吧。

wowcoller 发表于 2010-3-22 10:37:00

这个你用时间探测器跟踪下就出来了，然后用事件查询分析器来执行就好了亚

hdf007 发表于 2010-3-25 16:18:42

从错误的信息来看，在升级单据模板的时候出现了问题
要知道问题在哪个地方也很简单，你就把贴出来的语句分段执行，看看在哪里过不了就知道问题在哪里了

hdf007 发表于 2010-3-25 16:22:48

从错误的信息来看，在升级单据模板的时候出现了问题
要知道问题在哪个地方也很简单，你就把贴出来的语句分段执行，看看在哪里过不了就知道问题在哪里了
第一步执行：
--升级所有的单据模版
declare @TblCount smallint
declare @TblName varchar(30)
declare @iNoOld_VT varchar(20)
declare @iNoNew_VT varchar(20)
set @TblName = 'InvoiceItem_SAl'
set @TblCount=1
while (@TblCount<=7)
   begin
----升级显示格式模版
exec ('Insert Into VoucherItems(VT_ID,CardNum,CardItemNum,CardItemName,CanNotSelect,
         ShowIt,LinkIt,LinkTbl,tablename,fieldname,fieldtype,
         refertype,refertable,cardsection,cardformula1,
         cardformula2,CanModify,MaxLength,MaxShowLen,NumPoint,
         CanDelete,UserCheck,UserPrompt,FormatChar,IsMain,
         NeedSum,CalcField,LinkField,AliasNum,isSelfdef,cox,coy,tabindex,Height,Width)
Select -10000,CardNum,CardItemNum,CardItemName,CanNotSelect,
         ShowIt,LinkIt,LinkTbl,tablename,fieldname,fieldtype,
         refertype,refertable,cardsection,cardformula1,
         cardformula2,CanModify,MaxLength,MaxShowLen,NumPoint,
         CanDelete,UserCheck,UserPrompt,FormatChar,IsMain,
         NeedSum,CalcField,LinkField,AliasNum,isSelfdef,cox-datalength(CardItemName)*90,coy,tabidx,275,maxshowlen+datalength(CardItemName)*90
From ' + @TblName)
----升级打印格式模版
exec ('Insert Into VoucherItems_Prn(VT_ID,CardNum,CardItemNum,CardItemName,CanNotSelect,
         ShowIt,LinkIt,LinkTbl,tablename,fieldname,fieldtype,
         refertype,refertable,cardsection,cardformula1,
         cardformula2,CanModify,MaxLength,MaxShowLen,NumPoint,
         CanDelete,UserCheck,UserPrompt,FormatChar,IsMain,
         NeedSum,CalcField,LinkField,AliasNum,isSelfdef,cox,coy,tabindex,Height,Width)
Select -10000,CardNum,CardItemNum,CardItemName,CanNotSelect,
         ShowIt,LinkIt,LinkTbl,tablename,fieldname,fieldtype,
         refertype,refertable,cardsection,cardformula1,
         cardformula2,CanModify,MaxLength,MaxShowLen,NumPoint,
         CanDelete,UserCheck,UserPrompt,FormatChar,IsMain,
         NeedSum,CalcField,LinkField,AliasNum,isSelfdef,cox-datalength(CardItemName)*90,coy,tabidx,275,maxshowlen+datalength(CardItemName)*90
From ' + @TblName)

如果通过了，再执行第二步：
set @iNoNew_VT=1
declare Cur_VoucherTemplates cursor for SELECT CardNum
    FROM VoucherItems where VT_ID=-10000
OPEN Cur_VoucherTemplates
FETCH next FROM Cur_VoucherTemplates INTO @iNoOld_VT
WHILE @@FETCH_STATUS = 0
BEGIN
set @iNoNew_VT=(select VT_ID From VoucherTemplates
Where VoucherTemplates.VT_CardNumber = @iNoOld_VT and
VoucherTemplates.VT_TemplateMode=0)
----更新显示模版
update VoucherItems Set VT_ID =@iNoNew_VT Where CardNum=@iNoOld_VT
                update Vouchers Set DEF_ID =@iNoNew_VT Where CardNumber=@iNoOld_VT
----更新打印模版
set @iNoNew_VT=(select VT_ID From VoucherTemplates
Where VoucherTemplates.VT_CardNumber = @iNoOld_VT and
VoucherTemplates.VT_TemplateMode=1)
update VoucherItems_Prn Set VT_ID =@iNoNew_VT Where CardNum=@iNoOld_VT
                update Vouchers Set DEF_ID_PRN =@iNoNew_VT Where CardNumber=@iNoOld_VT
FETCH NEXT FROM Cur_VoucherTemplates INTO @iNoOld_VT
end
CLOSE Cur_VoucherTemplates

这两步都通过了应该是没有问题了，上面的语句没有对语法进行仔细检查，用的时候自己试一下

cs209 发表于 2010-3-30 11:02:02

这个大单了，

liuliti 发表于 2010-3-30 16:44:27

你看看能不能看懂。
--1、无法将 NULL 值插入列 ＇VT_ID＇，表 ＇UFDATA_555_2007.dbo.VoucherItems＇；该列不允许空值。 INSERT 失败。
------------------------------------
insert into VoucherTemplates
(VT_ID,VT_Name,VT_CardNumber,VT_TemplateMode,VT_Width,VT_Height,VT_BodyTop,VT_BodyLeft,VT_BodyWidth,VT_BodyHeight,VT_SelfDef1,VT_SelfDef2,VT_SelfDef3,VT_Memo,VT_Lock,VT_TitleName,VT_TitleTop,VT_TitleLeft,VT_TitleFontState,VT_PageHeader,VT_BodyFixedCols,VT_BodyMaxRows,VT_GridStyle,VT_WorkAreaColor,VT_FiexdColor,VT_ControlStyle,VT_GridStyleHead,VT_GridStyleBody,VT_GridstyleTotal,VT_GridPrnRows,VT_GridPrnRowHeight,VT_Header,VT_Foot,VT_footHeaderFont,VT_PrintTemplID,VT_AutoCalc,VT_PageSubTotal,VT_PageTotal,VT_PrintGrid,nAutoCalcWidth,nPrintSeril,nPrintGridLine,varPrintSetting)
select
VT_ID,＇＇ as VT_Name,VT_CardNumber,VT_TemplateMode,VT_Width,VT_Height,VT_BodyTop,VT_BodyLeft,VT_BodyWidth,VT_BodyHeight,VT_SelfDef1,VT_SelfDef2,VT_SelfDef3,VT_Memo,VT_Lock,VT_TitleName,VT_TitleTop,VT_TitleLeft,VT_TitleFontState,VT_PageHeader,VT_BodyFixedCols,VT_BodyMaxRows,VT_GridStyle,VT_WorkAreaColor,VT_FiexdColor,VT_ControlStyle,VT_GridStyleHead,VT_GridStyleBody,VT_GridstyleTotal,VT_GridPrnRows,VT_GridPrnRowHeight,VT_Header,VT_Foot,VT_footHeaderFont,VT_PrintTemplID,VT_AutoCalc,VT_PageSubTotal,VT_PageTotal,VT_PrintGrid,nAutoCalcWidth,nPrintSeril,nPrintGridLine,varPrintSetting from ufdata_001_2010..VoucherTemplates
WhereVT_ID not in (select VT_ID from VoucherTemplates)
--------------------------------------
--2、将 varchar 值 ＇IA01＇ 转换为数据类型为 int 的列时发生语法错误。

select * from AccInformation order by cid desc
可以看到 cid 有非数值
------------------------------
把AccInformation表中的cvalue ,cname,ccaption长度设成２倍长；

delete fromAccInformation where csysid in (＇OM＇,＇SA＇,＇IA＇)
insert into AccInformation (csysid,cid,cname,ccaption,ctype,cvalue,cdefault,bvisible,benable)
select csysid,cid,cname,ccaption,ctype,cvalue,cdefault,bvisible,benable from ufdata_001_2010..AccInformation where csysid in (＇OM＇,＇SA＇,＇IA＇)
--------------------------------
--3、违反了 PRIMARY KEY 约束 ＇PK_VT_ID＇。不能在对象 ＇VoucherTemplates＇ 中插入重复键。
------------
delete from VoucherTemplates where VT_ID in (＇20008＇,＇20009＇,＇20010＇,＇20012＇)
------------
--4、违反了 PRIMARY KEY 约束 ＇PK_VoucherPrefabricate＇。不能在对象 ＇VoucherPrefabricate＇ 中插入重复键。
-----------------------
delete fromVoucherPrefabricate where CardNumber in (＇QM01＇,＇QM02＇,＇QM03＇,＇QM04＇,＇QM05＇,＇QM06＇,＇QM07＇,＇QM08＇,＇QM09＇,＇QM10＇,＇QM11＇,＇QM12＇,＇QM13＇,＇QM14＇,＇QM15＇,＇QM16＇,＇QM17＇,＇QM18＇,＇QM19＇,＇QM20＇) andcContent = ＇手工输入＇
-----------------------

--5、错误信息:
数据库名: UFDATA_555_2007
D:\U8SOFT\Admin\SQLFILE8610\Main\Ufdata\data\zh-CN\Data_Upda_PU_Query_CN.sql
错误信息:
-2147467259
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。
执行如下语句时出错:
--
DECLARE @ID INT
DECLARE @TempIDS INT
SET @ID=(SELECT ID FROM rpt_glbdef_base WHERE Name=＇采购入库单列表过滤＇ and localeid=＇zh-CN＇)
SET @TempIDS= (SELECT TOP 1 MAX(Location)＋1 FROM Rpt_fltDef_base WHERE ID=@ID and localeid=＇zh- CN＇)
IF NOT EXISTS(SELECT ID FROM Rpt_fltDef_base WHERE localeid=＇zh- CN＇ AND Name=＇copyrdlist.cWhCode＇ AND ID=@ID)
Insert into Rpt_fltDef_base (, ,,,, ,,,,,,, ,,,,, ) Values ( ＇zh-CN＇,@TempIDS,@ID,＇copyrdlist.cWhCode＇,＇仓库编码＇,＇5,仓库档案＇,2,2,0, Null,1,0,0,0,0,0,0,1 )
----------------
因找不到具体的原因，在不影响升级程序正常的情况下，可以在admin.rar文件中找到：Data_Upda_PU_Query_CN.sql文件,并找到如下语句：
SET @ID=(SELECT ID FROM rpt_glbdef_base WHERE Name=＇采购入库单列表过滤＇ and localeid=＇zh-CN＇)
改成：SET @ID=(SELECT top 1 ID FROM rpt_glbdef_base WHERE Name=＇采购入库单列表过滤＇ and localeid=＇zh-CN＇)
即增加了一个top 1

ibmm2008 发表于 2010-4-3 08:31:52

数据库名: UFDATA_001_1999
D:\U8SOFT\Admin\SQLFILE850\Ufdata\DPRD_Public.sql
你852数据有问题。这是在提升在升级850出错

升级有二个文件，还有个文件是如何写的。
还有升级不代表数据库有问题。
还有种情况就是文件数据库有问题。

qisuo3 发表于 2010-10-6 20:47:24

这个只能一步步的慢慢看，是很麻烦，而且就算升级成功了也不表示以后就没有后遗症

jkserver 发表于 2010-11-3 16:50:05

根据我的经验.不要升级了.把数据导出来,然后装新的U890.在把数据导进去..你升级出的错误太多了,还未必成功.我以前从872到890升级还出了N多错误

yuhuazhi 发表于 2010-11-15 16:44:04

看不懂，升级出问题真麻烦，棘手呀

xtztyy 发表于 2010-12-29 18:23:45

我遇到过这个问题，提交集团，工程师回复，说是视图的问题，考到其他同事的电脑上升级就好了！感觉应该还是环境问题

498098148 发表于 2011-10-24 23:00:41

来学习学习，请多多关照:):):)

查看完整版本: U852升级至U890时提示：升级失败! 高手给解决啊!