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

Anything inside of a transaction will be part of that transaction. If you don't want it to be part of that transaction then do not put it inside.

Answer 3

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.

Answer 4

Depending on permissions, you could call out using xp_cmdshell to OSQL thereby creating an entirely separate connection. You might be able to do something similar with the CLR, although I've never tried it. However, I strongly advise against doing something like this.

Your best bet is to establish what the conventions are for your code and the calling code - what kind of a contract is supported between the two. You could make it a rule that your code is never called within another transaction (probably not a good idea) or you could give requirements on what the calling code is responsible for when an error occurs.