SQL Server: how to INSERT out of transaction control?

Hello Experts.
I'm writing a stored procedure with SQL Server (it should work on 2005~2008); besides doing many tasks, this SP fills out a table with log information about what's going on.
All operations are under transaction control; upon error, the transaction is rolled back.
But I would like to keep my log records!
So... is it possible to perform an INSERT INTO LogTable that is NOT under transaction control? So that a rollback will destroy any INSERT, DELETE, etc except those on LogTable.
Thank you!
LVL 8
lomo74Asked:
Who is Participating?
 
BrandonGalderisiConnect With a Mentor Commented:
begin tran
declare @Log table (id identity not null,LogDttm datetime default getutcdate(),LogData nvarchar(max))

insert into @log(LogData)values(N'SomeMessage')
rollback

After your rollback, you will see the table still contains data.
0
 
BrandonGalderisiCommented:
If you have a transaction being controlled within your procedure, then you can log to a table variable (which are not effected by transaction rollbacks) and then in your error code move the data to a permanent logging table.  But if you are within a transaction that is started outside of the application, your only hope would be to return the data and have the calling process handle the logging.  Transactions are meant to control what does and does not get into the DB.  there is no concept of a non-transaction bound statement within a transaction with the exception of with table variables.
0
 
lomo74Author Commented:
ok, thank you, I guessed as much...
would you be so kind as to provide a small example of writing to a table variable?
BTW no, the transaction is not started by an outside application, but by the SP itself --
0
 
lomo74Author Commented:
wonderful. that solves 100%.
thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.