Could not complete cursor operation because the table schema changed after the cursor
-
SQL JOB FAILURE
Error:
Could not complete cursor operation because the table schema changed after the cursor was declared. [SQLSTATE 42000] (Error 16943) Associated statement is not prepared [SQLSTATE HY007] (Error 0) Could not complete cursor operation because the table schema changed after the cursor was declared. [SQLSTATE 42000] (Error 16943). The step failed.
I need help. I’ve been giving a new opportunity to work in the ADM department for the company I’m employed by for the past three years; I actually applied for the position from an internal posting. Anyway, I really do enjoy it and I’m learning a lot, especially because of forums and all the great support from the veterans of SQL. My problem is I have a daily task where I review all scheduled jobs that run the previous night and troubleshoot failures. For the most part in the business line I’m is are when files are not sent to a ftp from our client, no big deal, and sometimes the maintenance plans fail because our hard drive “disk” space is limited. I’ve learned to fix the problems and everything is going well (now in my 4 month.) However there is one job that is ran every night and it does not fail every time but it seems that way. It has six steps that are each stored procedures, the error above is what fails the job and my only work around at this point is I restart the job on step 2 and it’s successful. Besides having to mannulally kick this job off three to four and sometimes five times a week it takes about 3 hours to complete with success. If it just ran at night when were not here it would be ok, but having to kick it off durning business hours is not good.
Here is each step and detail: Any help on fixing it to not fail would be awesome. Oh yea, I did not create this, but have been giving the task to keep up with it.
STEP 1
exec cm915CatA
set nocount on
truncate table unmatched_stage
insert into unmatched_stage
(PostingDate, gjahr, postperiod, ebeln, ebelp,
wrbtr2, werks, bukrs, [id], meins, lmein, bprme,
UM_CNV, BP_CNV, NetPrice, lgort, name1, ematn,
lifnr, awkey1, awkey2, buzei, fp1, fp2,
oldvendornum, oldprodnum)
select cast(f.budat as smalldatetime),
f.gjahr,
f.postperiod,
f.ebeln,
f.ebelp,
amt = case when f.SHKZG = 'H'
then (cast(f.wrbtr as money) *-1)
else cast(f.wrbtr as money) end,
f.werks,
f.bukrs,
f.id,
p.meins,
p.lmein,
p.bprme,
cast(p.umrez as float)/cast(p.umren as float) as UM_CNV,
cast(p.bpumz as float)/cast(p.bpumn as float) as BP_CNV,
cast(p.netpr as money)/cast(p.peinh as int) as NetPrice,
p.lgort,
p.name1,
p.ematn,
p.lifnr,
substring(awkey, 1, 10),
substring(awkey, 11, 4),
f.buzei,
'01',
'2007',
v.OldVendorNum,
m.OldProdNum
from
dpc_fi f left outer join
dpc_po p on f.ebeln = p.ebeln and f.ebelp = p.ebelp left outer join
dpc_vend v on p.lifnr = v.vendor left outer join
dpc_material m on (p.ematn = m.MaterialNum and f.werks = m.plant) left outer join
dpc_ignore i
on substring(f.awkey,1,10) = i.sap
where hkont = '0000203001'
and cast(budat as smalldatetime) <= getdate()
and p.loekz is null
and i.sap is null
delete from unmatched_stage
where wrbtr2 = 0
set nocount off
STEP 2
exec cm915bEOM
DECLARE @FY as char(4), @AWK as char(10), @BUZ as int, @id as int
,@BEWTP as char(1), @Mult as varchar(2), @lfbnr as varchar(10), @Qty as decimal(15,2), @Rec as varchar(16)
set nocount on
DECLARE DPC_1 CURSOR FOR
select gjahr, awkey1, buzei, [id]
from
unmatched_stage
where ebeln is not null
OPEN DPC_1
FETCH NEXT FROM DPC_1 into @FY, @AWK, @BUZ, @Id
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE DPC_2 CURSOR FOR
Select bewtp, Mult = case when bewtp in ('R', 'K','Q') and shkzg = 'H' then '-1'
WHEN bewtp in ('R', 'K','Q') and shkzg = 'S' THEN '1'
WHEN bewtp in ('E') and shkzg = 'S' THEN '-1'
WHEN bewtp in ('E') and shkzg = 'H' THEN '1' END
,lfbnr, cast(menge as decimal(15,2)), Receiver = case when bewtp = 'E' then frbnr else null end
FROM
DPC_GR
where FY = @FY
and belnr = @AWK
and cast(buzei as int) = @Buz
and bewtp in ('R', 'K')
OPEN DPC_2
FETCH NEXT FROM DPC_2 into @BEWTP, @Mult, @lfbnr, @Qty, @Rec
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE unmatched_stage
set I_BEWTP = @BEWTP, Mult = @Mult, I_LFBNR = @lfbnr, Qty = @qty, frbnr = @rec
where id = @id
FETCH NEXT FROM DPC_2 into @BEWTP, @Mult, @lfbnr, @Qty, @Rec
END
CLOSE DPC_2
DEALLOCATE DPC_2
FETCH NEXT FROM DPC_1 into @FY, @AWK, @BUZ, @id
END
CLOSE DPC_1
DEALLOCATE DPC_1
DECLARE DPC_3 CURSOR FOR
select gjahr, awkey1, buzei, [id]
from
unmatched_stage
where ebeln is not null
and I_bewtp is null
OPEN DPC_3
FETCH NEXT FROM DPC_3 into @FY, @AWK, @BUZ, @Id
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE DPC_4 CURSOR FOR
Select bewtp, Mult = case when bewtp in ('R', 'K','Q') and shkzg = 'H' then '-1'
WHEN bewtp in ('R', 'K','Q') and shkzg = 'S' THEN '1'
WHEN bewtp in ('E') and shkzg = 'S' THEN '-1'
WHEN bewtp in ('E') and shkzg = 'H' THEN '1' END
,lfbnr, cast(menge as decimal(15,2)), Receiver = case when bewtp = 'E' then frbnr else null end
FROM
DPC_GR
where
--FY = case when @fy in ('2003','2004') then FY else @FY end
belnr = @AWK
and cast(buzei as int) = 1
and bewtp = 'E'
OPEN DPC_4
FETCH NEXT FROM DPC_4 into @BEWTP, @Mult, @lfbnr, @Qty, @Rec
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE unmatched_stage
set I_BEWTP = @BEWTP, Mult = @Mult, I_LFBNR = @lfbnr, Qty = @qty, frbnr = @rec
where id = @id
FETCH NEXT FROM DPC_4 into @BEWTP, @Mult, @lfbnr, @Qty, @Rec
END
CLOSE DPC_4
DEALLOCATE DPC_4
FETCH NEXT FROM DPC_3 into @FY, @AWK, @BUZ, @id
END
CLOSE DPC_3
DEALLOCATE DPC_3
DECLARE DPC_5 CURSOR FOR
select gjahr, awkey1, buzei, [id]
from
unmatched_stage
where ebeln is not null
and I_bewtp is null
OPEN DPC_5
FETCH NEXT FROM DPC_5 into @FY, @AWK, @BUZ, @Id
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE DPC_6 CURSOR FOR
Select bewtp, Mult = case when bewtp in ('R', 'K','Q') and shkzg = 'H' then '-1'
WHEN bewtp in ('R', 'K','Q') and shkzg = 'S' THEN '1'
WHEN bewtp in ('E') and shkzg = 'S' THEN '-1'
WHEN bewtp in ('E') and shkzg = 'H' THEN '1' END
,lfbnr, cast(menge as decimal(15,2)), Receiver = case when bewtp = 'E' then frbnr else null end
FROM
DPC_GR
where FY = @FY
and belnr = @AWK
and cast(buzei as int) = 1
and bewtp = 'Q'
OPEN DPC_6
FETCH NEXT FROM DPC_6 into @BEWTP, @Mult, @lfbnr, @Qty, @Rec
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE unmatched_stage
set I_BEWTP = @BEWTP, Mult = @Mult, I_LFBNR = @lfbnr, Qty = @qty, frbnr = @rec
where id = @id
FETCH NEXT FROM DPC_6 into @BEWTP, @Mult, @lfbnr, @Qty, @Rec
END
CLOSE DPC_6
DEALLOCATE DPC_6
FETCH NEXT FROM DPC_5 into @FY, @AWK, @BUZ, @id
END
CLOSE DPC_5
DEALLOCATE DPC_5
set nocount off
STEP 3:
exec cm915c
set nocount on
delete
from
unmatched_stage
where ebeln is null
delete from unmatched_stage
where i_lfbnr in
(
select i_lfbnr
from
unmatched_stage
where I_lfbnr is not null
group by i_lfbnr
having sum(wrbtr2) = 0
)
delete from
unmatched_stage
where
id in
(
select a1.id
from
unmatched_stage a1,
(
select werks, lgort, oldvendornum, ebeln, ebelp, I_lfbnr
from
unmatched_stage
group by werks, lgort, oldvendornum, ebeln, ebelp, I_lfbnr
having sum(wrbtr2) = 0
) as a2
where a1.werks = a2.werks
and a1.lgort = a2.lgort
and a1.oldvendornum = a2.oldvendornum
and a1.ebeln = a2.ebeln
and a1.ebelp = a2.ebelp
and a1.I_lfbnr is null
and a2.I_lfbnr is null
)
------
delete from unmatched_stage
where
(ebeln in ('D502302735','D502405541','D500011550',
'D568200397','D568200397','D568200397','D568200397',
'D568200397','D568200397','D506404673','D500011550',
'D568200397','D568200397','D568200397','D568200397',
'D568200397','D514303710','D516502151') and ebelp = '00010')
or
(ebeln in ('D500011550','D513503809') and ebelp = '00020')
or
(ebeln in ('D500004858') and ebelp = '00090')
or
(ebeln in ('D501102626','D501502299', 'D502102617','D502818229','D503900941','D505817238',
'D508818199','D516502030','D516404432','D516404553','D517818189','D518502008',
'D518611431','D519404162','D519302157','D519404747','D519404610','D519404591',
'D519404592','D547102608','D547701412','D550817815','D550817841','D551817820',
'D557404588','D557900178','D557502290','D557502006','D557501309','D500012726',
'D506817936','D505302083','D501102626','D501403986'))
--------removing older lines that should not be on report----------------------
delete from unmatched_stage
where ebeln + ebelp in
('D51330196600010','D51360726100030','D51781682900010','D51840466600010','D51981810900010',
'D51981810900020','D51981810900030','D51981810900040','D51981810900050','D51981810900070',
'D51981810900080','D51981810900090','D51981810900110','D51981810900120','D51981810900130',
'D51981810900150','D51981810900160','D51981810900170')
-------added
-------run select sum(wrbtr2) from unmatched_stage to see that the above cancel to zero---
delete from unmatched_stage
where ebeln = 'D568104895' and ebelp = '00030' and postperiod = '6'
-----added
delete from unmatched_stage
where ebeln = 'D512407702' and ebelp = '00010'
-----added
select sum(wrbtr2)
from
unmatched_stage
set nocount off
STEP 4:
exec cm915c2
update dpc_gr
set ignore = null
where ignore = 'X'
-----PROCEDURE USED TO LINK UNMATCHED TYPE Q RECORDS WITH THE PROPER RECORD IN THE DPC_GR TABLE----------
DECLARE @id as int, @awk as varchar(15), @fy as varchar(5),
@ebeln as varchar(15), @ebelp as varchar(10),
@wrbtr2 as money, @werks as varchar(5),
@BEWTP as varchar(15), @MULT as varchar(2),
@LFBNR as varchar(15), @QTY as decimal(15,2), @BUZEI as varchar(10)
DECLARE DPC_10 CURSOR FOR
select id, awkey1, gjahr, ebeln, ebelp, wrbtr2, werks
from unmatched_stage
where postingdate > '2004-03-11'
and i_bewtp = 'Q'
order by awkey1, wrbtr2
OPEN DPC_10
FETCH NEXT FROM DPC_10 into @id, @awk, @fy, @ebeln, @ebelp, @wrbtr2, @werks
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE DPC_11 CURSOR FOR
select top 1 bewtp, mult = case when shkzg = 'H' then '-1' else '1' end, lfbnr,
qty = cast(menge as decimal(15,2)), buzei
from dpc_gr
where bewtp = 'Q'
and belnr = @awk
and fy = @fy
and ebeln = @ebeln
and ebelp = @ebelp
and cast(@wrbtr2 as decimal(15,2)) =
case when shkzg = 'H' then cast(arewr as decimal(15,2))*-1
else cast(arewr as decimal(15,2)) end
and werks = @werks
and ignore is null
order by fy, belnr, buzei
OPEN DPC_11
-----FETCH ONLY THE FIRST MATCHING ROW FROM THE DPC_GR TABLE------------------
FETCH NEXT FROM DPC_11 INTO @BEWTP, @MULT, @LFBNR, @QTY, @BUZEI
WHILE @@FETCH_STATUS = 0
BEGIN
-----UPDATE UNMATCHED STAGE WITH 1ST LINE OF MATCHING DPC_GR LINE-------------
update unmatched_stage
set I_bewtp = @bewtp, mult = @mult, I_lfbnr = @lfbnr, QTY = @QTY, buz2 = @buzei
where @ID = ID
-----UPDATE MATCHING DPC_GR LINE SO IT WILL BE IGNORED NEXT TIME AROUND--------
update dpc_gr
set ignore = 'X'
where @FY = FY AND @AWK = BELNR AND @BUZEI = BUZEI
-----FETCH NEXT MATCH FROM DPC_GR TABLE
FETCH NEXT FROM DPC_11 INTO @BEWTP, @MULT, @LFBNR, @QTY, @BUZEI
END
CLOSE DPC_11
DEALLOCATE DPC_11
-----FETCH NEXT TYPE Q DOCUMENT-------------------------------------------------
FETCH NEXT FROM DPC_10 into @id, @awk, @fy, @ebeln, @ebelp, @wrbtr2, @werks
END
CLOSE DPC_10
DEALLOCATE DPC_10
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
STEP 5:
exec cm915c
set nocount on
delete
from
unmatched_stage
where ebeln is null
delete from unmatched_stage
where i_lfbnr in
(
select i_lfbnr
from
unmatched_stage
where I_lfbnr is not null
group by i_lfbnr
having sum(wrbtr2) = 0
)
delete from
unmatched_stage
where
id in
(
select a1.id
from
unmatched_stage a1,
(
select werks, lgort, oldvendornum, ebeln, ebelp, I_lfbnr
from
unmatched_stage
group by werks, lgort, oldvendornum, ebeln, ebelp, I_lfbnr
having sum(wrbtr2) = 0
) as a2
where a1.werks = a2.werks
and a1.lgort = a2.lgort
and a1.oldvendornum = a2.oldvendornum
and a1.ebeln = a2.ebeln
and a1.ebelp = a2.ebelp
and a1.I_lfbnr is null
and a2.I_lfbnr is null
)
------
delete from unmatched_stage
where
(ebeln in ('D502302735','D502405541','D500011550',
'D568200397','D568200397','D568200397','D568200397',
'D568200397','D568200397','D506404673','D500011550',
'D568200397','D568200397','D568200397','D568200397',
'D568200397','D514303710','D516502151') and ebelp = '00010')
or
(ebeln in ('D500011550','D513503809') and ebelp = '00020')
or
(ebeln in ('D500004858') and ebelp = '00090')
or
(ebeln in ('D501102626','D501502299', 'D502102617','D502818229','D503900941','D505817238',
'D508818199','D516502030','D516404432','D516404553','D517818189','D518502008',
'D518611431','D519404162','D519302157','D519404747','D519404610','D519404591',
'D519404592','D547102608','D547701412','D550817815','D550817841','D551817820',
'D557404588','D557900178','D557502290','D557502006','D557501309','D500012726',
'D506817936','D505302083','D501102626','D501403986'))
--------removing older lines that should not be on report----------------------
delete from unmatched_stage
where ebeln + ebelp in
('D51330196600010','D51360726100030','D51781682900010','D51840466600010','D51981810900010',
'D51981810900020','D51981810900030','D51981810900040','D51981810900050','D51981810900070',
'D51981810900080','D51981810900090','D51981810900110','D51981810900120','D51981810900130',
'D51981810900150','D51981810900160','D51981810900170')
-------added
-------run select sum(wrbtr2) from unmatched_stage to see that the above cancel to zero---
delete from unmatched_stage
where ebeln = 'D568104895' and ebelp = '00030' and postperiod = '6'
-----added
delete from unmatched_stage
where ebeln = 'D512407702' and ebelp = '00010'
-----added
select sum(wrbtr2)
from
unmatched_stage
set nocount off
STEP 6:
exec cm915_current
truncate table UnmatchedCurrent
insert into unmatchedcurrent
select [id],PostingDate, gjahr,postperiod, ebeln,ebelp,wrbtr2,I_BEWTP,Mult,I_LFBNR,Qty, frbnr
,meins,lmein, bprme, werks, UM_CNV,BP_CNV, NetPrice, bukrs,lgort,name1, ematn,lifnr, oldvendornum,oldprodnum
,GR.strDeliveryNote
from
unmatched_stage
left outer join sntcatsql2.dpcprod.dbo.GoodsReceiptHeader GR
ON unmatched_stage.I_LFBNR = GR.idGrNumber
-
>>Oh yea, I did not create this, but have been giving the task to keep up with it.
If at all possible you should track down the original author and insert a large, sharp pointy object in them.
Nested cursors to perform an update, because the person didn't understand relational databases and set-based operations ? You would be much better off just re-writing some of this mess, both for your own sanity and long term maintainability.
-
Here's an example to get you started. This is a single UPDATE that removes the need for cursors DPC_1 and DPC_2 and the 2 WHILE loops.
When you express it like this, you also start to notice some glaring issues. For example, look at the 2 CASE statements - I've copied them as is from your code. Notice how they're checking the BEWTP column ? And comparing it to a value of 'E' ? Now look at your WHERE filter - you only select records where bewtp IN ('R', 'K') - why all the expressions for a type 'E" when you explcitly filter to include only 'R' and 'K' ?
UPDATE u
SET I_BEWTP = d.bewtp,
Mult = CASE
WHEN d.bewtp in ('R', 'K','Q') and shkzg = 'H' THEN '-1'
WHEN d.bewtp in ('R', 'K','Q') and shkzg = 'S' THEN '1'
WHEN d.bewtp in ('E') and shkzg = 'S' THEN '-1'
WHEN d.bewtp in ('E') and shkzg = 'H' THEN '1'
END,
I_LFBNR = d.lfbnr,
Qty = cast(d.menge as decimal(15,2)),
frbnr = case when d.bewtp = 'E' then d.frbnr else null end
FROM unmatched_stage As u
INNER JOIN DPC_GR As d
On ( u.gjahr = d.FY AND
u.awkey1 = d.beln AND
u.buzei = cast(d.buzei as int)
)
WHERE d.bewtp IN ('R', 'K')
-
What your saying seems very logical; I don't have a clue to be very honest. It seems I'm in a delema on this messed up job; I'm not advanced enough to re-write this and don't have a clue about cursors. Like you said, what's up with the 'E' It seems those responable for this mess include a employee not here anymore and my current boss; and yea he's the sensative type; So I am going to have to do some smooth talking to say hey, what do think about this, is the 'E' necessary, what do you think? It's tough being a new b on this team, I have a boss who's been doining it for 9 years and one other ahead of me doing for 7 years. I'm expected to learn on my own and have a lot of that to do. As for this problem, the non-use of cursors would keep the failure from occurring.
Thank you.
-
On another side note... Is there any other job or proc that is ran at the same time that this job is run? If any script is ran that alters the table in any way, then that error will occur. That also includes triggers if I remember correctly.
Also I'd really check into deleting all those cursors. The job will probabely run in less than 5 minutes (maybe only a few secs) once they are all gone and the indexes properly adjusted.
-
I ran your example as a select statement:
SELECT * ,
Mult = CASE
WHEN d.bewtp in ('R', 'K','Q') and shkzg = 'H' THEN '-1'
WHEN d.bewtp in ('R', 'K','Q') and shkzg = 'S' THEN '1'
WHEN d.bewtp in ('E') and shkzg = 'S' THEN '-1'
WHEN d.bewtp in ('E') and shkzg = 'H' THEN '1'
END,
I_LFBNR = d.lfbnr,
Qty = cast(d.menge as decimal(15,2)),
frbnr = case when d.bewtp = 'E' then d.frbnr else null end
FROM unmatched_stage As u
INNER JOIN DPC_GR As d
On ( u.gjahr = d.FY AND
u.awkey1 = d.belnr AND
u.buzei = cast(d.buzei as int)
)
WHERE d.bewtp IN ('R', 'K')
This currently returns:
(2443 row(s) affected)
So is the update you exampled, this entire part could be removed/replaced (?):
cm915bEOM DECLARE @FY as char(4), @AWK as char(10), @BUZ as int, @id as int ,@BEWTP as char(1), @Mult as varchar(2), @lfbnr as varchar(10), @Qty as decimal(15,2), @Rec as varchar(16) set nocount on DECLARE DPC_1 CURSOR FOR select gjahr, awkey1, buzei, [id] from unmatched_stage where ebeln is not null OPEN DPC_1 FETCH NEXT FROM DPC_1 into @FY, @AWK, @BUZ, @Id WHILE @@FETCH_STATUS = 0 BEGIN DECLARE DPC_2 CURSOR FOR Select bewtp, Mult = case when bewtp in ('R', 'K','Q') and shkzg = 'H' then '-1' WHEN bewtp in ('R', 'K','Q') and shkzg = 'S' THEN '1' WHEN bewtp in ('E') and shkzg = 'S' THEN '-1' WHEN bewtp in ('E') and shkzg = 'H' THEN '1' END ,lfbnr, cast(menge as decimal(15,2)), Receiver = case when bewtp = 'E' then frbnr else null end FROM DPC_GR where FY = @FY and belnr = @AWK and cast(buzei as int) = @Buz and bewtp in ('R', 'K') OPEN DPC_2 FETCH NEXT FROM DPC_2 into @BEWTP, @Mult, @lfbnr, @Qty, @Rec WHILE @@FETCH_STATUS = 0 BEGIN UPDATE unmatched_stage set I_BEWTP = @BEWTP, Mult = @Mult, I_LFBNR = @lfbnr, Qty = @qty, frbnr = @rec where id = @id FETCH NEXT FROM DPC_2 into @BEWTP, @Mult, @lfbnr, @Qty, @Rec END CLOSE DPC_2 DEALLOCATE DPC_2 FETCH NEXT FROM DPC_1 into @FY, @AWK, @BUZ, @id END CLOSE DPC_1 DEALLOCATE DPC_1
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply