查询库龄分析出现未响应的现象，无法查询

【问题现象】

查询库龄分析出现未响应想象，无法查询！登录999演示账套是能正常查询的！

【问题原因】

库龄分析的数据库存储过程有问题，需要重建存储过程

【处理方案】

执行附件里的脚本可以解决问题

1、在操作之前请先进行数据备份，以防数据出错后无法恢复！

2、执行附件中的脚本：大概要等半个小时左右，建议多等一会。

问题即可解决。

已根据您发的账套修改了，请您视实际情况修改

set @year=2006  （为账套启用年度）

while  @year<2020（为账套最后年度）

@cAccNo = N'206',（ 206 为账套的账套号）

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ST_InvRdBalance_test]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[ST_InvRdBalance_test]

GO

--计算年度账套的期初

CREATE   PROCEDURE [dbo].[ST_InvRdBalance_test]

@cAccNo Varchar(20),

@Year int

As

Declare @iPreYear As smallint

Declare @iYear As smallint

Declare @iFirst As bit

Declare @Acc  As Varchar(20)

Declare @PreAcc  As Varchar(20)

Declare @sSQL As nVarchar(1000)

Declare @inum as int

Declare @sBegSQL As nVarchar(1000)

Declare @CurDate As datetime

begin tran T1

--获得账套信息

Set @iFirst = 1

declare cur_no cursor for

Select distinct iYear

From UFSystem..UA_Account_Sub

Where cAcc_Id = @cAccNO And iYear < 9999 and iyear<=@year

Order by iYear

Open cur_no

Fetch Next From cur_no Into @iYear

While @@Fetch_Status = 0

Begin

--创建账套期初余额明细

Set @Acc = 'UFDATA_' + @cAccNO + '_' + cast(@iYear as Varchar(4))

Set @sSQL = N'select @Num = count(*) from ' + @Acc + '..sysobjects ' + ' Where name = ''ST_BegInvAge'''

exec sp_executesql @sSQL,N'@Num int output', @iNum output

--第一个年度时，将导入记录类型为34的期初数据

If @iFirst = 1

Begin

if @iNum = 0

Begin

--创建期初余额表

Set @sBegSQL = N' 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],

[cInvCode], [iNum], [iQuantity], [iUnitCost], [iPrice], [cBatch], [cFree1],

[cFree2], [dVDate], [cDefine22], [cDefine23], [cDefine24], [cDefine25],

[cDefine26], [cDefine27], [cItem_class], [cItemCode], [cName], [cItemCName]

Into '+ @Acc +'..ST_BegInvAge From '+@Acc+'..Rdrecord r Join ' + @Acc + '..Rdrecords rs On (r.id = rs.id) Where cVouchType = ''34'' '

exec sp_executesql @sBegSQL

Set @iFirst = 0

End

End

Else  --计算其他

Begin

--上一个账套名称

Set @PreAcc = 'UFDATA_' + @cAccNO + '_' + cast(@iYear - 1 as Varchar(4))

--计算账套期末结存

--取上期最大日期

Set @CurDate = (Select dEnd from UFSystem..ua_period

Where cAcc_id = @cAccNo

And iYear = @iYear - 1

And iId = (

Select Max(iID)

From UFSystem..ua_period

Where cAcc_id = @cAccNo

And iYear = @iYear - 1 ))

--计算上年余额，产生本年期初

if @iNum = 0

Begin

Exec ST_CalBegInvAge @PreAcc, @CurDate, @Acc

End

End

Fetch Next From cur_no Into @iYear

End

close cur_no

deallocate cur_no

commit tran T1

GO

DECLARE @return_value int

declare @year int

set @year=2006

while  @year<2020

begin

EXEC @return_value = [dbo].[ST_InvRdBalance_test]

@cAccNo = N'206',

@year=@year

    set @year=@year +1

end

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ST_InvRdBalance_test]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[ST_InvRdBalance_test]

GO