Hi,
I have to correct some data in SQL Server 2008 R2 database for an accounting system that was accidently over written. Basically I've to take stock items with fixed prices and compare those fixed prices against Sales Order Transactions for the stock item and correct entries where the transaction is not set to the correct price at the time of transaction.
I wrote SQL in a query window on my development database that uses two cursors one to retrieve Stock Items and the other to retrieve transactions and basically if the transaction price does not match the Stock item standard price at the time of the transaction it updates the transaction. This all ran well in the development environment so rather than retype everything I simply saved the window out to a SQL script file.
The issue I have is that when I open the Script file on the live instance (even though I'm logged in as sa) it doesn't change anything.
I tried running in debug mode and I can see each step being performed with no error messages but the UPDATE does not happen.
HERE IS MY CODE:
DECLARE @HistoryID bigint, @ItemID bigint, @TransPrice decimal(18,5), @TransDate DateTime, @StandardCost decimal(18,5)
--GET STANDARD PRICED STOCK ITEMS - THEY WILL BELONG TO A PRODUCT GROUP WITH
--STANDARD COSTING METHOD AND WILL BE NON LABOUR ITEMS
DECLARE CUR_Items CURSOR FOR SELECT StockItem.ItemID FROM ProductGroup INNER JOIN StockItem ON ProductGroup.ProductGroupID = StockItem.ProductGroupID WHERE (ProductGroup.CostingMethodID = 2) AND (ProductGroup.StockItemTypeID <> 2)
OPEN CUR_Items
FETCH NEXT FROM CUR_Items INTO @ItemID
WHILE @@FETCH_STATUS =0
BEGIN
DECLARE Cur_Trans CURSOR FOR SELECT TransactionHistoryID,UnitCostPrice, TransactionDate FROM TransactionHistory WHERE (TransactionTypeID =15) AND (ItemID = @ItemID)
OPEN Cur_Trans
FETCH NEXT FROM Cur_Trans INTO @HistoryID, @TransPrice, @TransDate
WHILE @@FETCH_STATUS = 0
BEGIN
SET @StandardCost = (SELECT TOP 1 StandardCostValue FROM StockItemStandardCostHistory WHERE (ChangedDate <=@TransDate) AND (ItemID=@ItemID) ORDER BY ChangedDate DESC)
IF (@TransPrice <> @StandardCost)
BEGIN
--This QUERY runs with no error in debug but data stays the same
UPDATE TransactionHistory SET UnitCostPrice = @StandardCost, CostValue = (Quantity * @StandardCost) WHERE (TransactionHistoryID = @ItemID)
END
FETCH NEXT FROM Cur_Trans INTO @HistoryID, @TransPrice, @TransDate
END
CLOSE Cur_Trans
DEALLOCATE Cur_Trans
FETCH NEXT FROM CUR_Items INTO @ItemID
END
CLOSE CUR_Items
DEALLOCATE CUR_Items