SQL Server Transactions how can I commit my transaction

Question

I have SQL Server 2005 stored procedure. Someone one is calling my stored procedure within a transaction. In my stored proc I'm logging some information (insert into a table). When the higher level transaction rolls back it removes my insert.

Is there anyway I can commit my insert and prevent the higher level rollback from removing my insert?

Thanks

Answer 1

Even if you start a new transaction, it will be nested within the outer transaction. SQL Server guarantees that a rollback will result in an unmodified database state. So there is no way you can insert a row inside an aborted transaction.

Here's a way around it, it's a bit of a trick. Create a linked server with rpc out = true and remote proc transaction promotion = false. The linked server can point to the same server as your procedure is running on. Then, you can use execte (<query>) at <server> to execute something in a new transaction.

if OBJECT_ID('logs') is not null drop table logs
create table logs (id int primary key identity, msg varchar(max))
if OBJECT_ID('TestSp') is not null drop procedure TestSp
go
create procedure TestSp as
execute ('insert into dbo.logs (msg) values (''test message'')') at LINKEDSERVER
go
begin transaction
exec TestSp
rollback transaction
select top 10 * from logs

This will end with a row in the log table, even though the transaction was rolled back.

Here's example code to create such a linked server:

IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND 
        srv.name = N'LINKEDSERVER')
    EXEC master.dbo.sp_dropserver @server=N'LINKEDSERVER', 
        @droplogins='droplogins'
EXEC master.dbo.sp_addlinkedserver @server = N'LINKEDSERVER', 
    @srvproduct=N'LOCALHOST', @provider=N'SQLNCLI', @datasrc=N'LOCALHOST', 
    @catalog=N'DatabaseName'
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVER', @optname=N'rpc out', 
     @optvalue=N'true'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LINKEDSERVER', 
    @useself=N'True', @locallogin=NULL,@rmtuser=NULL, @rmtpassword=NULL
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVER', 
    @optname=N'remote proc transaction promotion', @optvalue=N'false'

Answer 2

In Oracle you would use autonomous transactions for that, however, SQL Server does not support them.

It is possible to declare a table variable and return it from your stored procedure.

The table variables survive the ROLLBACK, however, the upper level code should be modified to read the variable and store its data permanently.