Commit transaction outside the current transaction (like autonomous transaction in Oracle)

Question

I need to write into a log table from a stored procedure. Now this log info has to survive a rollback offcourse.

I know this question has been asked before, but my situation is different and I cannot find an answer to my problem in these questions.

When there is no error in the stored procedure things are simple, the entry in the logtable will just be there.
When there is an error than things are complicated.
Inside the procedure I can do rollback in the catch and then insert the data into the log table, I know that and I am already doing that.
But the problem is when the stored procedure is called like this :

begin transaction
exec myStoredProcedure
rollback transaction
select * from myLogTable

I know this code makes not much sense, I kept it mimimal to demonstrate my problem.
If the caller of the stored procedure does the commit/rollback then it does not matters what I do in the stored procedure. My logentry will always be rolled back.

I also cannot use the temporary table trick, which is to return the data I want to log and let the caller use that data to insert it into the logtable after it has done the rollback, because the caller is an external application that I do not have the source from.

The logging is done in a seperate procedure that only has one line of code, the insert into the logtable.
What I need is a way to commit the insert in this procedure, outside the current transaction so it survives any rollback.

Is there a way to do this ?

The Solution:

I used lad2025 answer and thus far it is working without problems or performance issues.
But this procedure will only be called about 1000 times each day which is not that much so I guess I don't have to expect any problems either.

Answer 1

So you really search for some sort of Autonomous transaction (like in Oracle).

One ugly way to simulate it is to use loopback linked server.

Warning: This is PoC (I would think twice before I would use it in PROD) and do a lot of testing.

DECLARE @servername SYSNAME;
SET     @servername = CONVERT(SYSNAME, SERVERPROPERTY(N'ServerName'));

EXECUTE sys.sp_addlinkedserver
            @server = N'loopback', 
            @srvproduct = N'', 
            @provider = N'SQLNCLI', 
            @datasrc = @servername;

EXECUTE sys.sp_serveroption 
            @server = N'loopback', 
            @optname = 'RPC OUT', 
            @optvalue = 'ON';

EXECUTE sys.sp_serveroption 
        @server = N'loopback', 
        @optname = 'remote proc transaction promotion',
        @optvalue = 'OFF';

And code:

DROP TABLE IF EXISTS myLogTable;
CREATE TABLE myLogTable(i INT IDENTITY(1,1),
                        d DATETIME DEFAULT GETDATE(),
                        t NVARCHAR(1000));
GO

CREATE OR ALTER PROCEDURE my_logging
   @t NVARCHAR(MAX)
AS
BEGIN
   INSERT INTO myLogTable(t) VALUES (@t);
END;
GO

CREATE OR ALTER PROCEDURE myStoredProcedure
AS
BEGIN
    -- some work
    SELECT 1;
    INSERT INTO myLogTable(t) 
    VALUES ('Standard logging that will perish after rollback');

    EXEC loopback.T1.dbo.my_logging 
       @t = N'some custom loging that should survive rollback';
END;

Final call:

begin transaction
exec myStoredProcedure
rollback transaction
select * from myLogTable

Output:

i   d          t
2   2017-08-17 some custom loging that should survive rollback

Answer 2

It is quite interesting topic so let's check how MS approaches it.

First documentation: Migrating-Oracle-to-SQL-Server-2014-and-Azure-SQL-DB.pdf

Page 152.

Simulating Oracle Autonomous Transactions

This section describes how SSMA for Oracle V6.0 handles autonomous transactions (PRAGMA AUTONOMOUS_TRANSACTION). These autonomous transactions do not have direct equivalents in Microsoft SQL Server 2014.

When you define a PL/SQL block (anonymous block, procedure, function, packaged procedure, packaged function, database trigger) as an autonomous transaction, you isolate the DML in that block from the caller's transaction context. The block becomes an independent transaction started by another transaction, referred to as the main transaction.

To mark a PL/SQL block as an autonomous transaction, you simply include the following statement in your declaration section: PRAGMA AUTONOMOUS_TRANSACTION;

SQL Server 2014 does not support autonomous transactions. The only way to isolate a Transact-SQL block from a transaction context is to open a new connection.

Use the xp_ora2ms_exec2 extended procedure and its extended version xp_ora2ms_exec2_ex, bundled with the SSMA 6.0 Extension Pack, to open new transactions. The procedure's purpose is to invoke any stored procedure in a new connection and help invoke a stored procedure within a function body. The xp_ora2ms_exec2 procedure has the following syntax:

xp_ora2ms_exec2
<active_spid> int,
<login_time> datetime,
<ms_db_name> varchar,
<ms_schema_name> varchar,
<ms_procedure_name> varchar,
<bind_to_transaction_flag> varchar,
[optional_parameters_for_procedure];

Then you need to install on your server stored procedures and other scripts: SSMA for Oracle Extension Pack (only SSMA for Oracle Extension Pack.7.5.0.msi).

Your stored procedure will become:

CREATE TABLE myLogTable(i INT IDENTITY(1,1),
                        d DATETIME DEFAULT GETDATE(),
                        t NVARCHAR(1000));
GO

CREATE OR ALTER PROCEDURE my_logging
   @t NVARCHAR(MAX)
AS
BEGIN
   INSERT INTO myLogTable(t) VALUES (@t);
END;
GO

CREATE OR ALTER PROCEDURE myStoredProcedure
AS
BEGIN
    -- some work
    SELECT 1;
    INSERT INTO myLogTable(t) 
    VALUES ('Standard logging that will perish after rollback');

    DECLARE @login_time DATETIME = GETDATE();
    DECLARE @custom_text_to_log NVARCHAR(100);
    SET @custom_text_to_log=N'some custom loging that should survive rollback';
    DECLARE @database_name SYSNAME = DB_NAME();

    EXEC master.dbo.xp_ora2ms_exec2_ex 
       @@spid,
       @login_time,
       @database_name,
       'dbo',
       'my_logging',
       'N',
       @custom_text_to_log;
END;

And final call:

begin transaction
exec myStoredProcedure
rollback transaction
select * from myLogTable;

OUTPUT:

i   d          t
2   2017-08-21 some custom loging that should survive rollback