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

Question

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 is 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 normal behavior, but I need to exclude that log insert statement 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 real table 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 

Answer 1

Try using a table variable rather than a temp table. Table variables do not participate in a transaction.

http://zarez.net/?p=1977

http://www.sqlservercentral.com/blogs/steve_jones/2010/09/21/table-variables-and-transactions/

Answer 2

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:
http://sqlmag.com/t-sql/table-variable-tip

Answer 3

Check out the SAVE TRANSACTION command.

Answer 4

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