CODEWITHSUNDEEP
sql-servert-sqlloggingtransactions
Arvid
Arvid

Reputation: 181

TSQL logging inside transaction

I'm trying to write to a log file inside a transaction so that the log survives even if the transaction is rolled back.

--start code

begin tran

insert [something] into dbo.logtable

[[main code here]]

rollback

commit

-- end code

You could say just do the log before the transaction starts but that is not as easy because the transaction starts before this S-Proc is run (i.e. the code is part of a bigger transaction)

So, in short, is there a way to write a special statement inside a transaction that is not part of the transaction. I hope my question makes sense.

Upvotes: 18

Views: 12140

Answers (5)

Kiwi Nick
Kiwi Nick

Reputation: 150

Log output to a table, use a time delay, and use WITH(NOLOCK) to see it.

It looks like @arvid wanted to debug the operation of the stored procedure, and is able to alter the stored proc.

The c# code starts a transaction, then calls a s-proc, and at the end it commits or rolls back the transaction. I only have easy access to the s-proc

I had a similar situation. So I modified the stored procedure to log my desired output to a table. Then I put a time delay at the end of the stored procedure

WAITFOR DELAY '00:00:12';  -- 12 second delay, adjust as desired

and in another SSMS window, quickly read the table with READ UNCOMMITTED isolation level (the "WITH(NOLOCK)" below

SELECT * FROM dbo.NicksLogTable WITH(NOLOCK);

It's not the solution you want if you need a permanent record of the logs (edit: including where transactions get rolled back), but it suits my purpose to be able to debug the code in a temporary fashion, especially when linked servers, xp_cmdshell, and creating file tables are all disabled :-(

Apologies for bumping a 12-year old thread, but Microsoft deserves an equal caning for not implementing nested transactions or autonomous transactions in that time period.

Upvotes: 1

Ed Green
Ed Green

Reputation: 128

If you want to emulate nested transaction behaviour you can use named transactions:

begin transaction a

create table #a (i  int)

select * from #a
save transaction b

create table #b (i  int)
select * from #a
select * from #b

rollback transaction b

select * from #a
rollback transaction a

In SQL Server if you want a ‘sub-transaction’ you should use save transaction xxxx which works like an oracle checkpoint.

Upvotes: -3

ConcernedOfTunbridgeWells
ConcernedOfTunbridgeWells

Reputation: 66722

If the parent transaction rolls back the logging data will roll back as well - SQL server does not support proper nested transactions. One possibility is to use a CLR stored procedure to do the logging. This can open its own connection to the database outside the transaction and enter and commit the log data.

Upvotes: 2

KM.
KM.

Reputation: 103707

I do this one of two ways, depending on my needs at the time. Both involve using a variable, which retain their value following a rollback.

1) Create a DECLARE @Log varchar(max) value and use this: @SET @Log=ISNULL(@Log+'; ','')+'Your new log info here'. Keep appending to this as you go through the transaction. I'll insert this into the log after the commit or the rollback as necessary. I'll usually only insert the @Log value into the real log table when there is an error (in theCATCH` block) or If I'm trying to debug a problem.

2) create a DECLARE @LogTable table (RowID int identity(1,1) primary key, RowValue varchar(5000). I insert into this as you progress through your transaction. I like using the OUTPUT clause to insert the actual IDs (and other columns with messages, like 'DELETE item 1234') of rows used in the transaction into this table with. I will insert this table into the actual log table after the commit or the rollback as necessary.

Upvotes: 2

BradC
BradC

Reputation: 39986

Use a table variable (@temp) to hold the log info. Table variables survive a transaction rollback.

See this article.

Upvotes: 17

Related Questions