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