SQL Server: how to INSERT out of transaction control?

Posted on 2011-10-14
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
    LVL 39

    Expert Comment

    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.
    LVL 8

    Author Comment

    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

    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.
    LVL 8

    Author Comment

    wonderful. that solves 100%.
    thank you.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    This video discusses moving either the default database or any database to a new volume.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now