How will this affect the data using Loops with rollback transaction

Question

While @@Fetch_Status = 0
Begin
    INSERT INTO  [server].MyDatabase.dbo.Mytabletobeinserted (

    UPC,
    Sale_date)  
    VALUES(
    @UPC,
    @Sale_date)

    'Inserting the error trapping here'

    IF (@@ERROR <> 0)
    BEGIN
        ROLLBACK TRANSACTION;
        RETURN;
    END 

    Update t_sale_from_pos
    set been_sent = 'y' 
    where UPC = @UPC and sale_date=@sale_date

    Fetch Next from CursorSale
    into 
    @UPC,
    @Sale_date
end
close CursorSale

deallocate CursorSale

This stored procedure runs every day with a scheduler and the data is being passed over the network. When this stored procedure executes and then along the process of execution there is a sudden network time out. This executes every row to send to the other server over the network.

---

Now, my problem is:

• How will this affect the data calling the rollback transaction inside the loop?
• Will it read all the lines again and send it to the server for insertion or it will just read the row where it fails during the execution?

Need some advice. Thanks

Answer 1

You could try begin distributed transaction. Distributed transaction is a perfect fit in this case as distributed transactions were designed to span two or more servers. With transaction, even the system crashes, or there is a power cut, the system is still able to recover to its consistent state.

BEGIN DISTRIBUTED TRANSACTION;

BEGIN TRY
    //Your code here to create the cursor.
    While @@Fetch_Status = 0
    Begin
         INSERT INTO  [server].MyDatabase.dbo.Mytabletobeinserted(UPC,Sale_date)  
                                                          VALUES(@UPC,@Sale_date)

         Update t_sale_from_pos
         set been_sent = 'y' 
         where UPC = @UPC and sale_date=@sale_date

         Fetch Next from CursorSale into @UPC,@Sale_date
    END
    close CursorSale

    deallocate CursorSale
END TRY
BEGIN CATCH
    close CursorSale

    deallocate CursorSale

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

Actually, with distributed transactions you don't need to use cursor. In your case, it's better to also consider avoiding concurrency issues by creating a temp table. The reason for this is: the insert statement may take time and while it's inserting data, your table may be updated by another user and the update statement occurring after that may update wrong rows.

BEGIN DISTRIBUTED TRANSACTION;

BEGIN TRY
    CREATE TABLE #LocalTempTable(UPC int,Sale_date datetime)
    INSERT INTO #LocalTempTable(UPC,Sale_date)
    SELECT UPC,Sale_date 
    FROM YourTable

    INSERT INTO [server].MyDatabase.dbo.Mytabletobeinserted(UPC,Sale_date)  
    SELECT UPC,Sale_date 
    FROM #LocalTempTable

    Update t_sale_from_pos
    set been_sent = 'y' 
    where EXISTS (SELECT *
                  FROM #LocalTempTable
                  WHERE #LocalTempTable.UPC = t_sale_from_pos.UPC 
                        AND #LocalTempTable.Sale_date  = t_sale_from_pos.Sale_date)

END
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

Answer 2

If I understand you correctly. TRY/CATCH should help you -

WHILE @@FETCH_STATUS = 0 BEGIN

     BEGIN TRY

          INSERT INTO [server].MyDatabase.dbo.Mytabletobeinserted (UPC, Sale_date)  
          SELECT @UPC, @Sale_date

          UPDATE dbo.t_sale_from_pos
          SET been_sent = 'y' 
          WHERE UPC = @UPC 
          AND sale_date = @sale_date

     END TRY
     BEGIN CATCH

          UPDATE dbo.t_sale_from_pos
          SET been_sent = 'n' 
          WHERE UPC = @UPC 
          AND sale_date = @sale_date

     END CATCH

     FETCH NEXT FROM CursorSale INTO @UPC, @Sale_date

END

CLOSE CursorSale
DEALLOCATE CursorSale