Paul White NZ (7/15/2010)
The other solution, in SQL Server 2008, is to use a loopback linked server, using sp_serveroption to set remote proc transaction promotion off. This allows autonomous transactions too.
That's the simplest way to achieve it. Unfortunately it works only in SQL 2008 and I had to code in CLR because we're still on SQL 2005.
I also wrote an article about this last year, but decided it would not be of interest to sufficient people to make it worth publishing.
I strongly disagree. If it's well written (and I'm sure it is) it's always worth publishing. It's a subject that I see around sometimes and I think it can solve lots of problems. Autonomous transactions can save your life in many situations (sequences, logging, auditing) and other RDBMS vendors implement it natively (Oracle, DB2, Firebird, PostgreSQL...). It's a shame that MS hasn't decided yet to add this feature to SQL Server.
-- Gianluca Sartori