Script not executing queries

[G_Waddell]

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

[isozworld]

Hello,
I just changed your cursor options
And also be sure the table you are trying to update has no update trigger script.
And also, in Cur_Trans cursor your select script has (TransactionTypeID =15) condition but your update script has not.
I know even without this condition your update script must work.

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 local forward_only static 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 CUR_Items INTO @ItemID
WHILE @@FETCH_STATUS =0
BEGIN
DECLARE Cur_Trans CURSOR local forward_only static FOR  

 SELECT TransactionHistoryID,UnitCostPrice, TransactionDate FROM TransactionHistory WHERE (TransactionTypeID =15) AND (ItemID = @ItemID)
    OPEN Cur_Trans
    FETCH  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  Cur_Trans INTO @HistoryID, @TransPrice, @TransDate 
    END
    CLOSE Cur_Trans
    DEALLOCATE Cur_Trans

    FETCH CUR_Items INTO @ItemID
END
CLOSE CUR_Items
DEALLOCATE CUR_Items

[G_Waddell]

Hi,

That will certainly help with the performance but the UPDATE should still be happening...

[isozworld]

Hi,
print out @TransPrice and @StandardCost in your IF (@TransPrice <> @StandardCost) condition like

print @TransPrice print @StandardCost 

becuase if one of them is null then the update cannot perform correctly
this part of may return null value for @StandardCost

SET @StandardCost = (SELECT TOP 1 StandardCostValue FROM StockItemStandardCostHistory WHERE (ChangedDate <=@TransDate) AND (ItemID=@ItemID)

And also I rewrote your script try this one.
by the way it cant be tested for me.

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 local forward_only static 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 CUR_Items INTO @ItemID
WHILE @@FETCH_STATUS =0
BEGIN
        UPDATE TranHist
        SET   TranHist.UnitCostPrice = StockItemStan.StandardCostValue
        FROM TransactionHistory TranHist  INNER JOIN  StockItemStandardCostHistory StockItemStan
        ON   TranHist.TransactionHistoryID = StockItemStan.ItemID AND  TranHist.UnitCostPrice<>StockItemStan.StandardCostValue
        AND StockItemStan.ChangedDate <= TranHist.TransactionDate


    FETCH CUR_Items INTO @ItemID
END
CLOSE CUR_Items
DEALLOCATE CUR_Items