Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Script in SQL 2000 does not work in 2008

Author  Topic 

Vack
Aged Yak Warrior

530 Posts

Posted - 2013-02-20 : 09:41:02
The following script below was written in SQL 2000 and worked fine. Now that we have moved to SQL 2008 it errors out with;
(1 row(s) affected)
Msg 16947, Level 16, State 1, Line 21
No rows were updated or deleted.
The statement has been terminated.

Line 21 is:UPDATE LarryAlkon

LarryAlkon is a view.


DECLARE @req_ship_dt varchar(20), @qty_to_ship decimal(13,4), @qty_bkord decimal(13,4),
@qty_ordered decimal(13,4), @qty_on_hand decimal(13,4), @qty_allocated decimal(13,4), @so_qty_bkord decimal(13,4)
DECLARE abc CURSOR FOR
SELECT req_ship_dt, qty_to_ship, qty_bkord, so_qty_bkord, qty_ordered,
qty_on_hand, qty_allocated FROM LarryAlkon
ORDER BY req_ship_dt
for update of qty_to_ship, so_qty_bkord, qty_bkord;
OPEN abc
FETCH NEXT FROM abc INTO @req_ship_dt, @qty_to_ship, @qty_bkord,
@so_qty_bkord, @qty_ordered, @qty_on_hand, @qty_allocated
WHILE (@@FETCH_STATUS = 0)
BEGIN--While
DECLARE @available decimal(13,4)
SET @available = @qty_on_hand + @qty_bkord - @qty_allocated
IF (@available > 0) BEGIN --1
IF (@available > @so_qty_bkord) BEGIN --2
UPDATE LarryAlkon
SET qty_to_ship =(SELECT qty_ordered FROM oeordlin_sql WHERE ord_type=LarryAlkon.ord_type
AND ord_no=LarryAlkon.ord_no AND line_seq_no=LarryAlkon.line_seq_no), so_qty_bkord = 0
WHERE CURRENT OF abc
UPDATE LarryAlkon
SET qty_bkord = @qty_bkord-@so_qty_bkord
WHERE CURRENT OF abc
END --2
ELSE BEGIN --3
UPDATE LarryAlkon
SET qty_to_ship = @qty_to_ship + @available, so_qty_bkord = @so_qty_bkord - @available
WHERE CURRENT OF abc
UPDATE LarryAlkon
SET qty_bkord = @qty_bkord - @available
WHERE CURRENT OF abc
END --3
END --1
FETCH NEXT FROM abc INTO @req_ship_dt, @qty_to_ship,
@qty_bkord, @so_qty_bkord, @qty_ordered, @qty_on_hand, @qty_allocated
END --While
CLOSE abc
DEALLOCATE abc
GO
--ADDED BY PETE TO CLEAR BO QTY FLAG WHEN FILLED
UPDATE oeordlin_sql
SET qty_bkord = 0
WHERE (qty_to_ship - qty_ordered = 0) AND (qty_bkord <> 0)
GO
--ADDED BY PETE TO CALCULATE NEW BO QTY WHEN FILLED
UPDATE oeordlin_sql
SET qty_bkord = qty_ordered - qty_to_shiP
WHERE (ord_type = 'O') AND (qty_ordered - qty_to_ship <> qty_bkord) AND (bkord_fg = 'Y')
GO

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-02-20 : 10:17:10
see here: http://support.microsoft.com/kb/238547/en-us


Too old to Rock'n'Roll too young to die.
Go to Top of Page
   

- Advertisement -