Could not complete cursor operation because the table schema changed after the cursor
-
>>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 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply