用友T3升級報錯列名cMemo無效和對象名dboST_CalBegInvAge無效

1、用友T3恢復(fù)升級步驟1、 賬套恢復(fù)恢復(fù)賬套之前，請確認賬套號和賬套路徑是否按要求更改比如：賬套號為005，恢復(fù)路徑為：E:UFSMARTAdmin2、 升級賬套選擇需要升級的賬套及年度，并確認A如果出現(xiàn)以下情況: 對象名dbo.ST_CalBegInvAge無效請在數(shù)據(jù)庫2008中執(zhí)行文件：對象名dbo.ST_CalBegInvAge無效.sql步驟如下1在開始程序的打開SQL2008,如圖點連接進入選文件/打開/文件,路徑: D:用友相關(guān)點執(zhí)行就行了附:數(shù)據(jù)庫語句-賬套005年度為2014升級時，軟件報錯對象名dbo.ST_CalBegInvAge無效-請執(zhí)行以下語句use ufdata_0

2、05_2014alter table MatchVouch add bReqAnalyse bit,dIssueDate varchar(16)GO/* 對象: StoredProcedure dbo.ST_CalBegInvAge 腳本日期: 08/15/2014 13:12:13 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-計算上年結(jié)存庫齡CREATE PROCEDURE dbo.ST_CalBegInvAgecDBName Varchar(20), -上一個賬套名稱 CurrentDate Datetime, -計算日期cCurDBNa

3、me varchar(20) -當(dāng)前賬套名稱AsDeclare strLeftQty nVarchar(4000)Declare StockStartDate datetimedeclare cWhCode nvarchar(50)declare cInvCode nvarchar(50)declare cBatch nvarchar(50)declare cFree1 nvarchar(120)declare cFree2 nvarchar(120)declare iQtty floatdeclare iNum floatdeclare autoId intdeclare iVouchQtt

4、y floatdeclare iVouchNum floatdeclare iTempQtty floatdeclare iTempNum floatdeclare strSQLInvAge nVarchar(4000)declare strInvAge nVarchar(4000)declare strSQLIn nVarchar(4000)declare strSQLOut nVarchar(4000)declare strNewRecord nVarchar(4000)declare strSql nVarchar(200)declare sSQL nVarchar(200)declar

5、e sBegdate As nVarchar(200)declare iCou int-取庫存啟用日期Set sBegdate = N'select StockStartDate = cast(cValue as Datetime) from ' + cDBName + '.accinformation ' + ' Where cSysid=''ST'' and cName= ''dSTStartDate'' 'exec sp_executesql sBegdate,N'St

6、ockStartDate datetime output', StockStartDate output If StockStartDate is Null BeginReturn End-計算本期當(dāng)前結(jié)存Set strNewRecord = N'Insert Into ' +cDBName+'.STStockAgeTempTable(autoid, iqtty, inum) values(autoid, iTempQtty, iTempNum) 'set strLeftQty =' SELECT cWhCode,cInvCode,IsNull(

7、cBatch,'''') As cBatch,IsNull(cFree1,'''') As cFree1,IsNull(cFree2,'''') As cFree2,SUM(case when bRdFlag = 1 then iQuantity else -iQuantity end) as iQtty,SUM(case when bRdFlag = 1 then iNum else - iNum end) AS iNum From( Select bRdFlag, cWhCode, cInvCo

8、de, iQuantity, INum, cBatch, cFree1, cFree2From '+ cDBName+'.ST_BegInvAge Union ALLSelect R.bRdFlag, R.cWhCode, Rs.cInvCode, Rs.iQuantity, Rs.INum, Rs.cBatch, Rs.cFree1, Rs.cFree2From ' + cDBName+ '.RdRecord R inner join '+cDBName+'.RdRecords Rs on R.id = Rs.id Where R.dDate

9、>= StartDate And R.dDate <= CurrentDate And cVouchType <> ''33'' And cVouchType <> ''34'' ) Xgroup by cWhCode, cInvCode, IsNull(cBatch,''''),IsNull(cFree1,''''), IsNull(cFree2,'''') '-取入庫語句Set strSQ

10、LIn = ' Select AutoId, iQtty, iNum From (Select dDate, AutoId, abs(iQuantity) As iQtty, abs(iNum) As iNum From '+cDBName+'.ST_BegInvAgeWhere cWhCode = cWhCode and cInvCode = cInvCode and isnull(cBatch,'''') = cBatchand isnull(cFree1,'''') = cFree1 and isnu

11、ll(cFree2,'''') = cFree2 Union ALLSelect d.dDate,ds.autoid, Abs(ds.iQuantity) as iQtty, Abs(ds.iNum) as iNumfrom '+cDBName+'.RdRecord d join ' + cDBName + '.RdRecords ds on (d.id = ds.id) where ( (d.bRdFlag = 1 and ds.iQuantity > 0) or (d.bRdFlag <> 1 and ds.

12、iQuantity < 0)And d.dDate >= StartDate and d.dDate <= CurrentDate and d.cWhCode = cWhCode and ds.cInvCode = cInvCode and IsNull(ds.cBatch,'''') = cBatch and IsNull(ds.cFree1,'''') = cFree1and IsNull(ds.cFree2,'''') = cFree2) Xorder by dDate De

13、sc,AutoID Desc for read only 'Set strSQLIn = N' declare curInvIn insensitive cursor for ' + strSQLInset strLeftQty = N'declare curRd cursor for ' + strLeftQtyexecute sp_executesql strLeftQty,N'CurrentDate Datetime, StartDate Datetime',CurrentDate, StockStartDateopen curRd

14、fetch next from curRd into cWhCode,cInvCode,cBatch,cFree1,cFree2,iQtty, iNum-建立計算結(jié)果臨時表Set sSQL = N'select Num = count(1) from ' + cDBName + '.sysobjects ' + ' Where name = ''STStockAgeTempTable'' 'exec sp_executesql sSQL,N'Num int output', iCou output

15、If iCou <> 0 BeginExec (' Drop Table ' + cDBName+'.STStockAgeTempTable')EndExec (' Create Table '+cDBName+'.STStockAgeTempTable (autoId int , iQtty float, iNum float)')-sp_help ST_BegInvAgewhile fetch_status = 0 beginif(iQtty < 0 ) set iQtty = 0 if(iNum <

16、0 or iNum is null) set iNum = 0 Set iTempQtty = 0.0Set iTempNum = 0.0-按降序取入庫execute sp_executesql strSQLIn,N'CurrentDate Datetime, StartDate Datetime, cWhCode varchar(50), cInvCode varchar(20), cBatch varchar(50), cFree1 varchar(120), cFree2 varchar(120)',CurrentDate, StockStartDate, cWhCode

17、, cInvCode, cBatch, cFree1, cFree2 open curInvInfetch next from curInvIn into autoId, iVouchQtty, iVouchNumwhile (round(iQtty,6) > 0 or round(iNum,6) > 0) and fetch_status =0 beginif Round(iQtty, 6) > Round(iVouchQtty, 6)beginset iTempQtty = iVouchQttyset iQtty = Round(iQtty, 6) - Round(iVo

18、uchQtty, 6)end else beginset iTempQtty = iQttyset iQtty = 0.0end if Round(iNum, 6) > Round(iVouchNum,6)beginset iTempNum = iVouchNumset iNum = Round(iNum, 6) - Round(iVouchNum, 6)end else beginset iTempNum = iNumset iNum = 0.0end -增加計算結(jié)果execute sp_executesql strNewRecord,N'autoid int, iTempQt

19、ty float, iTempNum float',autoid, iTempQtty, iTempNumSet iTempQtty = 0.0Set iTempNum = 0.0fetch next from curInvIn into autoid, iVouchQtty, iVouchNumend close curInvIndeallocate curInvInfetch next from curRd into cWhCode,cInvCode,cBatch,cFree1,cFree2,iQtty, iNumendclose curRddeallocate curRd-將結(jié)果

20、數(shù)據(jù)輸出set strSQLInvAge =N'Select ID, bRdFlag,cVouchType,cWhCode ,dDate ,cCode,cRdCode,cDepCode ,cPersonCode ,cVenCode ,cHandler ,cMemo ,cMaker ,cDefine1 ,cDefine2 ,cDefine3 ,cDefine4 ,cDefine5 ,cDefine6 ,cDefine7 ,cDefine8 ,cDefine9 ,cDefine10 , RT.AutoID ,cInvCode,(case when x.bRdFlag = 0 then -1

21、 * RT.iNum else RT.iNum end) As iNum,(case when x.bRdFlag = 0 then -1 * RT.iQtty else RT.iQtty end) As iQuantity ,iUnitCost ,iPrice ,cBatch,cFree1 ,cFree2 ,dVDate ,cDefine22 ,cDefine23 ,cDefine24 ,cDefine25 ,cDefine26 ,cDefine27 ,cItem_class , cItemCode ,cName ,cItemCNameInto '+ cCurDBName+'

22、.ST_BegInvAgeFrom '+cDBName+'.STStockAgeTempTable RT Inner Join (Select ID, bRdFlag,cVouchType,cWhCode ,dDate ,cCode,cRdCode,cDepCode ,cPersonCode ,cVenCode ,cHandler ,cMemo ,cMaker ,cDefine1 ,cDefine2 ,cDefine3 ,cDefine4 ,cDefine5 ,cDefine6 ,cDefine7 ,cDefine8 ,cDefine9 ,cDefine10 ,AutoID ,

23、cInvCode,iNum ,iQuantity ,iUnitCost ,iPrice ,cBatch,cFree1 ,cFree2 ,dVDate ,cDefine22 ,cDefine23 ,cDefine24 ,cDefine25 ,cDefine26 ,cDefine27 ,cItem_class , cItemCode ,cName ,cItemCNameFrom '+cDBName+'.ST_BegInvAgeUnion All Select R.ID, bRdFlag,cVouchType,cWhCode ,dDate ,cCode,cRdCode,cDepCode ,cPersonCode ,cVenCode ,cHandler ,cMemo ,cMaker ,cDefine1 ,cDefine2 ,cDefine3 ,cDefine4 ,cDefine5 ,cDefine6 ,cDefine7 ,cDefine8 ,cDefine9 ,cDefine10 ,AutoID ,cIn