SQL Server: how to INSERT out of transaction control?

Posted on 2011-10-14
Medium Priority
Last Modified: 2012-05-12
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!
Question by:lomo74
  • 2
  • 2
LVL 39

Expert Comment

ID: 36968097
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.

Author Comment

ID: 36968160
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 --
LVL 39

Accepted Solution

BrandonGalderisi earned 500 total points
ID: 36968184
begin tran
declare @Log table (id identity not null,LogDttm datetime default getutcdate(),LogData nvarchar(max))

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

After your rollback, you will see the table still contains data.

Author Comment

ID: 36968233
wonderful. that solves 100%.
thank you.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question