If you ever did anything in SQL worth embedding into transaction(s), you were bound to encounter an age old problem of auditing failures in audit table(s) while at the same time rolling back the failed transaction. Problem ofcourse being that transaction rollbacks are efficient and blind in their duty and will leave no stone unturned while wiping out all evidence that your statements were ever executed on that server, this unfortunately including the inserts in the audit table. Kind of a catch 22 situation. Or was that the "chicken and the egg" dillema?

While trying to solve this bit of a conundrum at this forum post (serbian), an interesting proposition surfaced. Apparently, the Oracle server guys were a bit quicker to the gun than the MSSQL guys and actually created "pragma autonomous_transaction;", a new breed of transaction that actually gets executed OUTSIDE the context of currently running transaction. Yes indeed my friends, committing or rolling back your favorite transaction has no influence on autonomous transaction, nothing, zip, nada, it just does not care or wish to care. Sounds like a swell solution for our auditing problem, if only SQL guys bothered to implement it in SQL Server. A workaround has been proposed on this blog, however, and with no ill thoughts towards the author, I would not really base my business processes on the fact that rollback does not affect temporary tables; honestly it looks more like a bug than a feature to me. Since bugs get squashed eventually I would rather keep on looking forward to service packs than being afraid of them.

But, fret none, there is a light at the end of the tunnel and there are other solutions to explore. When you analyze a typical autonomous transactions the question pops up: what are they and how do they work? How do you leave your transaction context which is tied to your connection? Well, you open a new connection, of course. Sounds so simple it makes you wonder why it does not exist in SQL server, after all who can best open a connection to sql server but the sql server itself; hell, it could even bypass the networking protocol altogether and dive straight into the murky depths of secondary transaction (just as it does for its context connection). And how do you open a new connection from a SQL batch/stored procedure? Well, barring the gruesome extended stored procedures and some low level C++ programming, another approach is our majesty, the CLR (que fanfares). So without further ado, here's the CLR stored procedure that should do the trick:

using System.Data.SqlClient;
using System.Security.Principal;
using Microsoft.SqlServer.Server;

namespace AutonomousTransactionCLR
{
    public static class ExecContext
    {
        [SqlProcedure(Name = "ExecuteAutonomous"]
        public static void ExecuteAutonomous(string connectionString, string sqlText)
        {
            if (SqlContext.IsAvailable)
            {
                WindowsImpersonationContext impContext = null;
                try
                {
                    if (SqlContext.WindowsIdentity != null) impContext = SqlContext.WindowsIdentity.Impersonate();
                    using (SqlConnection conn = new SqlConnection(connectionString + ";enlist=false;"))
                    {
                        SqlCommand cmd = new SqlCommand(sqlText, conn);
                        conn.Open();
                        cmd.ExecuteNonQuery();
                    }
                }
                finally
                {
                    if (impContext != null) impContext.Undo();
                }
            }
         }
    }
}

Just deploy this to the SQL server's database where you need it (If you don't know how to deploy CLR stored procedure google for it, there are tons of examples). Since we are using SqlContext.Windows identity to impersonate the original caller be mindful that your assembly with this stored procedure MUST be registered with EXTERNAL ACCESS permission set, SAFE mode just won't cut it if we want to use integrated security. In this sample I had a sample log table called Test with one numeric field, and as expected, the value 123 remained in the table after the rollback.

begin transaction;
exec dbo.ExecuteAutonomous 'Data Source=MyServer;Initial Catalog=AdventureWorks;Integrated Security=True', 'insert into Test values (123)';
rollback;
select * from Test;

Now, the caveats . As you probably noticed from the source, we are "packing" the connection string as the first parameter of the procedure, and you rightfully ask 'Why?'. Well, the thing is we can't really use the loopback context connection (the one with "context connection=true;") as that one puts us directly in touch with our calling connection and its current transaction context. We cannot clone, get connection info or delist the connection from the transaction context its in so it is of very little use to us. As a consequence its up to us to pass the connection string (you can hardcode it in the CLR if that's your favorite flavor of icecream, I try to avoid it). Realistically, we can't expect much help here, for all intents and purposes the connection string of a calling connection is a client side thingy and the server is probably unaware of most of it, so it is better in general for us to somehow supply a connection string that will work in this situation. Just assume your CLR is a SQL client application trying to connect to a locally installed SQL server and write your connetion string as you would for that app.

Another thing that probably bugs you is why we are appending "enlist=false;" to the supplied connection string and for that we have to thank the ADO.NET guys. In v2 they really went out of their way to automate things for us, so if we try to open a connection to the same database as the calling connection is connected to it will kindly enlist us in caller's transaction context, probably assuming that nobody is crazy enough not to want that Well, we dont want it, and so we tell them that (at least they were observant enough to allow us to override their decision).

And, finally, a word to the wise. This is a completely different connection, emphasis on the different. Just as you don't get squashed by the callers transaction you also dont get to participate in it either. If isolation is such that you don't get to read dirty data, then you dont get to see anything a calling transaction wrote so far, so don't go trying to load it to make a copy in the audit log . Also, the locks are without mercy, so don't go racing against yourself, chances are you'll both lose and yield to a deadlock. Don't say I didn't warn ya.

That's about it, being fierce .NET developers you are, you can go ahead and tweak this to your liking. An alternative solution is also available on SQL Programmability blog using linked servers, but it basically boils down to the same thing (open a second connection and prevent the transaction from bubbling into it). Until such time comes that SQL Server guys get the message that we ought to have autonomous transactions too...