SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Script in SQL 2000 does not work in 2008
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Vack
Constraint Violating Yak Guru

USA
497 Posts

Posted - 02/20/2013 :  09:41:02  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 02/20/2013 :  10:17:10  Show Profile  Visit webfred's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000