T-SQL Equivalent of .NET TransactionScopeOption.Suppress

Question

In my .NET code, inside a database transaction (using TransactionScope), I could include a nested block with TransactionScopeOption.Suppress, which ensures that the commands inside the nested block are committed even if the outer block rolls back. Following is a code sample:

using (TransactionScope txnScope = new TransactionScope(TransactionScopeOption.Required))
{
    db.ExecuteNonQuery(CommandType.Text, "Insert Into Business(Value) Values('Some Value')");

    using (TransactionScope txnLogging = new TransactionScope(TransactionScopeOption.Suppress))
    {
        db.ExecuteNonQuery(CommandType.Text, "Insert Into Logging(LogMsg) Values('Log Message')");
        txnLogging.Complete();
    }

    // Something goes wrong here. Logging is still committed

    txnScope.Complete();
}

I was trying to find if this could be done in T-SQL. A few people have recommended OPENROWSET, but it doesn't look very 'elegant' to use. Besides, I think it is a bad idea to put connection information in T-SQL code.

I've used SQL Service Broker in past, but it also supports Transactional Messaging, which means message is not posted to the queue until the database transaction is committed.

My requirement: Our application stored procedures are being fired by some third party application, within an implicit transaction initiated outside stored procedure. And I want to be able to catch and log any errors (in a database table in the same database) within my stored procedures. I need to re-throw the exception to let the third party app rollback the transaction, and for it to know that the operation has failed (and thus do whatever is required in case of a failure).

@usr - I have no knowledge of third party app code. The .NET code I posted in my question was just to that I would like to do the same thing in T-SQL code. — M. Rashid, Apr 26 '16 at 07:50
@Shaneis - Do you mean table variables? How could these help? I want to log errors in a permanent database table that happens to reside in the same database the stored procedures are running from. — M. Rashid, Apr 26 '16 at 07:52

score 4 · Answer 1 · answered Apr 26 '16 at 08:04

4

You can set up a loopback linked server with the remote proc transaction Promotion option set to false and then access it in TSQL or use a CLR procedure in SQL server to create a new connection outside the transaction and do your work.

Both methods suggested in How to create an autonomous transaction in SQL Server 2008.

Both methods involve creating new connections. There is an open connect item requesting this functionality be provided natively.

answered Apr 26 '16 at 08:04

Martin Smith

438,706
87
741
845

CLR procedure option was under consideration. However loopback Linked Server option is a lot simpler. Thanks a lot! Any noticeable performance downsides of linked server option? This is only going to be used for exceptional conditions only (mostly in catch blocks of stored procedures). – M. Rashid Apr 26 '16 at 08:51
Two connections instead of one would be the main thing I can think of but doesn't sound as though this will be a regular thing. – Martin Smith Apr 26 '16 at 09:03

score 0 · Answer 2 · answered Apr 26 '16 at 08:12

Values in a table variable exist beyond a ROLLBACK.

So in the following example, all the rows that were going to be deleted can be inserted into a persisted table and queried later on thanks to a combination of OUTPUT and table variables.

-- First, create our table
CREATE TABLE [dbo].[DateTest] ([Date_Test_Id] INT IDENTITY(1, 1), [Test_Date] datetime2(3));

-- Populate it with 15,000,000 rows
-- from 1st Jan 1900 to 1st Jan 2017.
INSERT INTO [dbo].[DateTest] ([Test_Date])
SELECT 
TOP (15000000)
    DATEADD(DAY, 0, ABS(CHECKSUM(NEWID())) % 42734)
    FROM [sys].[messages] AS [m1]
    CROSS JOIN [sys].[messages] AS [m2];

BEGIN TRAN;

BEGIN TRY

    DECLARE @logger TABLE ([Date_Test_Id] INT, [Test_Date] DATETIME);

    -- Delete every 1000 row
    DELETE FROM [dbo].[DateTest]
    OUTPUT deleted.Date_Test_Id, deleted.Test_Date INTO @logger
    WHERE [Date_Test_Id] % 1000 = 0;

    -- Make it fail
    SELECT 1/0

    -- So this will never happen
    COMMIT TRANSACTION;

END TRY
BEGIN CATCH

    ROLLBACK TRAN
    SELECT * INTO dbo.logger FROM @logger;

END CATCH;

SELECT * FROM dbo.logger;

DROP TABLE dbo.logger;

The question states the "third party app" is rolling back the transaction. The rollback is not in the proc itself (though this would be a better solution if it is optional where this happens) — Martin Smith, Apr 26 '16 at 08:16

T-SQL Equivalent of .NET TransactionScopeOption.Suppress

2 Answers2

Linked