CODEWITHSUNDEEP
c#sql-servert-sqltransactionstransactionscope
M. Rashid
M. Rashid

Reputation: 179

T-SQL Equivalent of .NET TransactionScopeOption.Suppress

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).

Upvotes: 3

Views: 989

Answers (2)

Shaneis
Shaneis

Reputation: 1085

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;

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453395

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.

Upvotes: 4

Related Questions