Reputation: 5866

How to prevent a specific INSERT statement from rolling back in a transaction

I have a very huge SP and I have a transaction in it. I am running an algorithm in the SP and if the algorithm doesn't succeed, the transaction gets rolled back.

I need to log some data even if the transaction gets rolled back, but when the transaction is rolled back, it also rolls back the logs as well. This is a normal behavior, but I need to exclude those log insert statements from the the rollback, so the transaction still gets logged.

I have a temp table called #MissingAllocationLines, and I insert my logs into that table. Then if it rollbacks, I need to insert all rows from #MissingAllocationLines into a real table called DLWMS_ALLOCATIONMISSINGLOG

Is that possible? My sample code is below

create table #MissingAllocationLines
(ALLOCATIONJOBID BIGINT,
ORDERID BIGINT,
ORDERDETAILID BIGINT,
ITEMID BIGINT,
STOCKQUANTITY BIGINT,   
ORDERQUANTITY BIGINT)


BEGIN TRANSACTION

WHILE(.....)
BEGIN
    INSERT INTO #MissingAllocationLines (ALLOCATIONJOBID,ORDERID,ORDERDETAILID,ITEMID,STOCKQUANTITY,ORDERQUANTITY)
    VALUES (@ALLOCATIONJOBID,@OrderID,@OrderDetailID,@ItemID,ISNULL(@StockFreeQuantity, 0),ISNULL(@RemainingQuantity,0))
    ...
    ...
    ...
END

IF(@DONE=1)
BEGIN
    COMMIT TRANSACTION
END
ELSE
BEGIN
    ROLLBACK TRANSCATION

    INSERT INTO DLWMS_ALLOCATIONMISSINGLOG (ALLOCATIONJOBID,ORDERID,ORDERDETAILID,ITEMID,STOCKQUANTITY,ORDERQUANTITY)
    SELECT ALLOCATIONJOBID,ORDERID,ORDERDETAILID,ITEMID,STOCKQUANTITY,ORDERQUANTITY
    FROM #MissingAllocationLines
END

Upvotes: 4

Answers (4)

TheGameiswar

Reputation: 28900

In catch block,before rolling back the transaction,do the following...

 DECLARE @TABLE AS TABLE
    (COL1 INT,
     COL2 INT
    ...
     )
     INSERT INTO @TABLE 
     SELECT * FROM #TEMP TABLE

    ROLLBACK TRANSCATION

    INSERT INTO DLWMS_ALLOCATIONMISSINGLOG 
     select * from @table

References:
https://www.itprotoday.com/sql-server/table-variable-tip

Upvotes: 1

Kannan Kandasamy

Reputation: 13959

You can use 'remote proc transaction promotion' as false and do logging using that linked server to local server: Code as below

begin tran outertran
insert into t values (1)
begin tran innertran
insert into localserver.tempdb.#log values (1)
commit tran innertran
IF (@Done) 
begin 
     commit tran outertran
end
else begin
rollback tran outertran
end 

select * from #log

EXEC sp_addlinkedserver @server = N'localserver',@srvproduct = N'',@provider = N'SQLNCLI', @datasrc = @@SERVERNAME
EXEC sp_serveroption localserver,N'remote proc transaction promotion','FALSE'
EXEC sp_serveroption localserver,N'RPC OUT','TRUE' -- Enable RPC to the given server

Upvotes: 0