Operation Is Not Allowed When The Object Is Closed
Есть один TfrxADOQuery, который формирует список договоров - он мастер:
declare @date_beg datetime;
set @date_beg= :date_beg;
select p.pay_id, p.dog_num, p.pay_name, p.par_id, p.date_contract
from payer p inner join bills b on p.pay_id=b.pay_id
inner join advances ad on p.pay_id=ad.pay_id
where b.credit_rest>0
and b.conductsU=1
and b.advtype = 0
and b.TakedDebt is null
and b.percBill_type is null
and ad.credit_rest>0
and b.rperiod_beg < @date_beg
and ad.adv_date < @date_beg
group by p.pay_id, p.dog_num, p.pay_name, p.par_id, p.date_contract
order by p.dog_num
И еще один TfrxADOQuery, который берет ввиде параметра код договора и формирует по нему печатную форму уведомления:
declare @pay_id int;
declare @date_beg smalldatetime;
declare @rest money;
declare @min_bill int;
declare @min_adv int;
declare @adv_date smalldatetime;
declare @adv_paym int;
declare @S1 varchar(500);
declare @S2 varchar(1000);
declare @S_summ varchar(500);
declare @summ_money money;
set @date_beg= :date_beg;
set @pay_id = :pay_id;
set @S1='';
set @S2='';
set @S_summ='';
set @adv_date=13;
IF object_id('tempdb..##TmpBills') IS not Null drop table ##TmpBills
IF object_id('tempdb..##TmpAdv') IS not Null drop table ##TmpAdv
create table ##TmpBills (
num int IDENTITY(1,1),
b_date smalldatetime,
b_cr money,
adv_distr money default 0,
flag tinyint default 0)
insert into ##TmpBills (b_date,b_cr)
select b.rperiod_beg as b_date,
b.credit_rest as b_cr
from bills b
where b.pay_id=@pay_id
and b.conductsU=1
and b.advtype = 0
and b.TakedDebt is null
and b.percBill_type is null
and b.credit_rest<>0
and b.rperiod_beg < @date_beg
order by b.doc_date
select IDENTITY(INT,1,1) as num,
ad.adv_date as ad_date,
ad.credit_rest as ad_cr,
ad.payment_id as ad_paym
into ##TmpAdv from advances ad
where ad.pay_id=@pay_id
and ad.credit_rest<>0
and ad.adv_date < @date_beg
order by ad.adv_date
while ((select Count(*) from ##TmpAdv)>0) and ((select count(*) from ##TmpBills where flag=0)>0)
begin
set @min_bill = (select min(num) from ##TmpBills where flag=0);
set @min_adv = (select min(num) from ##TmpAdv);
if (select ad_paym from ##TmpAdv where num=@min_adv)<;>0
begin
set @adv_paym= (select ad_paym from #TmpAdv where num=@min_adv);
if @adv_date<;>(select datepart(mm,ad_date) from ##TmpAdv where num=@min_adv)
begin
set @adv_date= (select datepart(mm,ad_date) from ##TmpAdv where num=@min_adv);
set @S1= @S1+(select case @adv_date
when 1 then 'Январь'
when 2 then 'Февраль'
when 3 then 'Март'
when 4 then 'Апрель'
when 5 then 'Май'
when 6 then 'Июнь'
when 7 then 'Июль'
when 8 then 'Август'
when 9 then 'Сентябрь'
when 10 then 'Октябрь'
when 11 then 'Ноябрь'
when 12 then 'Декабрь'
end)+ ' '+ cast(year((select ad_date from ##TmpAdv where num=@min_adv))as varchar(4))+', ';
end
set @S2= @S2+(select (convert(varchar(10),doc_date,104)+' №'+cast(doc_num as varchar)+', ')
from payments where payment_id=@adv_paym);
end
set @rest = (select b_cr-adv_distr from ##TmpBills where num=@min_bill)
- (select ad_cr from ##TmpAdv where num=@min_adv)
if @rest=0
begin
update ##TmpBills set adv_distr=b_cr, flag=1 where num=@min_bill
delete ##TmpAdv where num=@min_adv
end
if @rest>;0
begin
update ##TmpBills set adv_distr=adv_distr+(select ad_cr from ##TmpAdv where num=@min_adv) where num=@min_bill
delete ##TmpAdv where num=@min_adv
end
if @rest<;0
begin
update ##TmpBills set adv_distr=b_cr, flag=1 where num=@min_bill
update ##TmpAdv set ad_cr=(-1)*@rest, ad_paym=0 where num=@min_adv
end
end
select @summ_money= cast(sum(adv_distr) as money) from ##TmpBills where adv_distr<>0
select ((case datepart(mm,b_date)
when 1 then 'Январь'
when 2 then 'Февраль'
when 3 then 'Март'
when 4 then 'Апрель'
when 5 then 'Май'
when 6 then 'Июнь'
when 7 then 'Июль'
when 8 then 'Август'
when 9 then 'Сентябрь'
when 10 then 'Октябрь'
when 11 then 'Ноябрь'
when 12 then 'Декабрь'
end)+ ' '+ cast(year(b_date)as varchar(4))) as bill_period,
adv_distr,
lower(rtrim(@S1))as adv_period,
substring(rtrim(@S2),1,len(@S2)-1)as adv_paym,
(select top 1 rtrim(persF)+' '+rtrim(persI) from self_pers where f_boss = 1 order by pers_id) as director,
summ_phrase=@S_summ
from ##TmpBills
where adv_distr<>0
order by num
drop table ##TmpBills
drop table ##TmpAdv
В результате запуска отчета, формируется страница с полным список договоров, но всего одна страница с уведомлением и только по первому договору и выводится ошибка "Operation is not allowed when the object is closed". Что в скриптах надо исправить?
P.S. во втором TfrxADOQuery в свойстве FieldAliases пусто.
declare @date_beg datetime;
set @date_beg= :date_beg;
select p.pay_id, p.dog_num, p.pay_name, p.par_id, p.date_contract
from payer p inner join bills b on p.pay_id=b.pay_id
inner join advances ad on p.pay_id=ad.pay_id
where b.credit_rest>0
and b.conductsU=1
and b.advtype = 0
and b.TakedDebt is null
and b.percBill_type is null
and ad.credit_rest>0
and b.rperiod_beg < @date_beg
and ad.adv_date < @date_beg
group by p.pay_id, p.dog_num, p.pay_name, p.par_id, p.date_contract
order by p.dog_num
И еще один TfrxADOQuery, который берет ввиде параметра код договора и формирует по нему печатную форму уведомления:
declare @pay_id int;
declare @date_beg smalldatetime;
declare @rest money;
declare @min_bill int;
declare @min_adv int;
declare @adv_date smalldatetime;
declare @adv_paym int;
declare @S1 varchar(500);
declare @S2 varchar(1000);
declare @S_summ varchar(500);
declare @summ_money money;
set @date_beg= :date_beg;
set @pay_id = :pay_id;
set @S1='';
set @S2='';
set @S_summ='';
set @adv_date=13;
IF object_id('tempdb..##TmpBills') IS not Null drop table ##TmpBills
IF object_id('tempdb..##TmpAdv') IS not Null drop table ##TmpAdv
create table ##TmpBills (
num int IDENTITY(1,1),
b_date smalldatetime,
b_cr money,
adv_distr money default 0,
flag tinyint default 0)
insert into ##TmpBills (b_date,b_cr)
select b.rperiod_beg as b_date,
b.credit_rest as b_cr
from bills b
where b.pay_id=@pay_id
and b.conductsU=1
and b.advtype = 0
and b.TakedDebt is null
and b.percBill_type is null
and b.credit_rest<>0
and b.rperiod_beg < @date_beg
order by b.doc_date
select IDENTITY(INT,1,1) as num,
ad.adv_date as ad_date,
ad.credit_rest as ad_cr,
ad.payment_id as ad_paym
into ##TmpAdv from advances ad
where ad.pay_id=@pay_id
and ad.credit_rest<>0
and ad.adv_date < @date_beg
order by ad.adv_date
while ((select Count(*) from ##TmpAdv)>0) and ((select count(*) from ##TmpBills where flag=0)>0)
begin
set @min_bill = (select min(num) from ##TmpBills where flag=0);
set @min_adv = (select min(num) from ##TmpAdv);
if (select ad_paym from ##TmpAdv where num=@min_adv)<;>0
begin
set @adv_paym= (select ad_paym from #TmpAdv where num=@min_adv);
if @adv_date<;>(select datepart(mm,ad_date) from ##TmpAdv where num=@min_adv)
begin
set @adv_date= (select datepart(mm,ad_date) from ##TmpAdv where num=@min_adv);
set @S1= @S1+(select case @adv_date
when 1 then 'Январь'
when 2 then 'Февраль'
when 3 then 'Март'
when 4 then 'Апрель'
when 5 then 'Май'
when 6 then 'Июнь'
when 7 then 'Июль'
when 8 then 'Август'
when 9 then 'Сентябрь'
when 10 then 'Октябрь'
when 11 then 'Ноябрь'
when 12 then 'Декабрь'
end)+ ' '+ cast(year((select ad_date from ##TmpAdv where num=@min_adv))as varchar(4))+', ';
end
set @S2= @S2+(select (convert(varchar(10),doc_date,104)+' №'+cast(doc_num as varchar)+', ')
from payments where payment_id=@adv_paym);
end
set @rest = (select b_cr-adv_distr from ##TmpBills where num=@min_bill)
- (select ad_cr from ##TmpAdv where num=@min_adv)
if @rest=0
begin
update ##TmpBills set adv_distr=b_cr, flag=1 where num=@min_bill
delete ##TmpAdv where num=@min_adv
end
if @rest>;0
begin
update ##TmpBills set adv_distr=adv_distr+(select ad_cr from ##TmpAdv where num=@min_adv) where num=@min_bill
delete ##TmpAdv where num=@min_adv
end
if @rest<;0
begin
update ##TmpBills set adv_distr=b_cr, flag=1 where num=@min_bill
update ##TmpAdv set ad_cr=(-1)*@rest, ad_paym=0 where num=@min_adv
end
end
select @summ_money= cast(sum(adv_distr) as money) from ##TmpBills where adv_distr<>0
select ((case datepart(mm,b_date)
when 1 then 'Январь'
when 2 then 'Февраль'
when 3 then 'Март'
when 4 then 'Апрель'
when 5 then 'Май'
when 6 then 'Июнь'
when 7 then 'Июль'
when 8 then 'Август'
when 9 then 'Сентябрь'
when 10 then 'Октябрь'
when 11 then 'Ноябрь'
when 12 then 'Декабрь'
end)+ ' '+ cast(year(b_date)as varchar(4))) as bill_period,
adv_distr,
lower(rtrim(@S1))as adv_period,
substring(rtrim(@S2),1,len(@S2)-1)as adv_paym,
(select top 1 rtrim(persF)+' '+rtrim(persI) from self_pers where f_boss = 1 order by pers_id) as director,
summ_phrase=@S_summ
from ##TmpBills
where adv_distr<>0
order by num
drop table ##TmpBills
drop table ##TmpAdv
В результате запуска отчета, формируется страница с полным список договоров, но всего одна страница с уведомлением и только по первому договору и выводится ошибка "Operation is not allowed when the object is closed". Что в скриптах надо исправить?
P.S. во втором TfrxADOQuery в свойстве FieldAliases пусто.