TransactionScope + Audit Table + Commit Transaction In Stored Procedure

Question

My company is using SQL Server 2008. I am facing a Audit Table with transaction problems.

I have a stored procedure.

create proc MySP
as begin
    insert into MY_TABLE values('Value1');

    begin transaction TX_MY_SP
    -- write to audit table permanently
    insert into AUDIT_TABLE values('Value1 is inserted.'); 
    commit transaction TX_MY_SP
end

I have a block of VB.net code

Using tx = New TransactionScope()
    Using conn = New SqlConnection(MY_CONN_STR)
        Using cmd = New SqlCommand("MySP", conn)
            conn.Open()
            cmd.ExecuteNonQuery()
            Throw New ApplicationException("Always throw exception.")
        End Using
    End Using
    tx.Complete()
End Using

However there is no record inserted into AUDIT_TABLE. I found the reason in MSDN http://msdn.microsoft.com/en-us/library/ms189336.aspx

My question is how can I insert records AUDIT_TABLE with stored procedure.

Thanks!

Answer 1

Any operation inside a transaction that rolled back rolls back as well. Not doing so would break transaction atomicity. Given that the activity you're auditing is being rolled back, is very likely that you actually want the audit to be rolled back anyway.

None the less, there are legitimate cases when one needs to have operations recorded outside the scope of the current transaction, like certain debug cases. There are known workarounds, like using event notifications for the user configurable event class, then using sp_trace_generateevent to cause the event notification activated procedure to run and record the audit. Because the profiler events are generated outside the transaction scope, the audit record does not get rolled back.

:setvar dbname testdb
:on error exit

set nocount on;
use master;

if exists (
    select * from sys.server_event_notifications
    where name = N'audit')
begin
    drop event notification audit on server;
end 
go

if db_id('$(dbname)') is not null
begin
    alter database [$(dbname)] set single_user with rollback immediate;
    drop database [$(dbname)];
end
go

create database [$(dbname)];
go

alter authorization on database::[$(dbname)] to [sa];
go

use [$(dbname)];
go

create queue audit;
create service audit on queue audit (
    [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
go  

create table audit_table (
    Time datetime not null,
    TextData nvarchar(256) not null);
go

create procedure usp_audit
as
begin
declare @h uniqueidentifier, @mt sysname, @mb varbinary(max), @mx xml;
begin transaction;
receive top(1) @h = conversation_handle,
    @mt = message_type_name,
    @mb = message_body
from audit;
if (@mt = N'http://schemas.microsoft.com/SQL/Notifications/EventNotification')
begin
    select @mx = cast(@mb as xml);
    insert into audit_table (Time, TextData)
    values (
        @mx.value(N'(/EVENT_INSTANCE/PostTime)[1]', N'datetime'),
        @mx.value(N'(/EVENT_INSTANCE/TextData)[1]', N'nvarchar(256)'));
end
else if (@mt = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
    or @mt = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
begin
    end conversation @h;
end 
commit
end 
go

alter queue audit
    with activation (
        status = on,
        procedure_name = usp_audit,
        max_queue_readers = 1,
        execute as owner);
go      

create event notification audit
on server for USERCONFIGURABLE_0
to service N'audit', N'current database';
go

begin transaction;
exec sp_trace_generateevent 82, N'this was inserted from a rolled back';
rollback
go

waitfor delay '00:00:05';
select * from audit_table;
go

Answer 2

When you use TransactionScope, you need to call the Complete method before going out of its scope if you don't want the transaction to rollback:

Using tx = New TransactionScope()
    Using conn = New SqlConnection(MY_CONN_STR)
        Using cmd = New SqlCommand("MySP", conn)
            conn.Open()
            cmd.ExecuteNonQuery()
            'Throw New ApplicationException("Always throw exception.")
        End Using
    End Using

    tx.Complete() ' <---- Here

End Using

Answer 3

Basically, what you can do is to have one async audit/log system. So your audit will be running on a different thread, and it does not matter whether you main transaction scope fails or not.

1. use enterprise library+MSMQ
2. build you own light-weight async log system ( use a sync queue)