bypass a transaction

Is there some way to bypass a transaction, when doing a insert. Meaning when
i'm inside a BEGIN TRANS and wants to do a INSERT that will not be removed i
f
a ROLLBACK is done, is there any way to do that?
Example:
BEGIN TRANSACTION
INSERT log_table values ( 'Survive' )
ROLLBACK TRANSACTION
Afterwards the log_table contains the 'Survive' row...
CheersTroy,
Try using a SAVEPOINT. See 'Transaction Savepoints' in the SQL BOL.
HTH
Jerry
"Troy" <Troy@.discussions.microsoft.com> wrote in message
news:2216FE6C-190B-40F8-8EA6-BC1621CF4EEE@.microsoft.com...
> Is there some way to bypass a transaction, when doing a insert. Meaning
> when
> i'm inside a BEGIN TRANS and wants to do a INSERT that will not be removed
> if
> a ROLLBACK is done, is there any way to do that?
> Example:
> BEGIN TRANSACTION
> INSERT log_table values ( 'Survive' )
> ROLLBACK TRANSACTION
> Afterwards the log_table contains the 'Survive' row...
> Cheers|||Hi Jerry and thanks for your answer :)
But i don't think SAVEPOINT is what i am lokking for, i'm not easy to please
;) Let me clear out the question..
Inside the transaction we have several update statements. For each update
statement (that takes quite a while) we want to log, to a table, showing
progress and status. But if the transactions rolls back our logging is also
rolled back, and this is what i am trying to avoid...
Cheers
"Jerry Spivey" wrote:

> Troy,
> Try using a SAVEPOINT. See 'Transaction Savepoints' in the SQL BOL.
> HTH
> Jerry
> "Troy" <Troy@.discussions.microsoft.com> wrote in message
> news:2216FE6C-190B-40F8-8EA6-BC1621CF4EEE@.microsoft.com...
>
>|||No can do, there are no such independent subtransactions in SQL Server.. Wha
t you can do is to log
to a table variable and after the transaction use that table variable to log
into a real table. You
will not be able to investigate during the transaction, though. A table vari
able is not rolled back.
Another option is to open a new connection (through an extended stored proce
dure, for example). This
is not a light-weigh solution, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Troy" <Troy@.discussions.microsoft.com> wrote in message
news:A65DF86B-0BE8-4836-A55B-3F95DD328E21@.microsoft.com...
> Hi Jerry and thanks for your answer :)
> But i don't think SAVEPOINT is what i am lokking for, i'm not easy to plea
se
> ;) Let me clear out the question..
> Inside the transaction we have several update statements. For each update
> statement (that takes quite a while) we want to log, to a table, showing
> progress and status. But if the transactions rolls back our logging is als
o
> rolled back, and this is what i am trying to avoid...
> Cheers
> "Jerry Spivey" wrote:
>|||Hi Tabor,
Thanks for your answer, that cleared it out. I must find another way to
solve this....
Cheers
"Tibor Karaszi" wrote:

> No can do, there are no such independent subtransactions in SQL Server.. W
hat you can do is to log
> to a table variable and after the transaction use that table variable to l
og into a real table. You
> will not be able to investigate during the transaction, though. A table va
riable is not rolled back.
> Another option is to open a new connection (through an extended stored pro
cedure, for example). This
> is not a light-weigh solution, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Troy" <Troy@.discussions.microsoft.com> wrote in message
> news:A65DF86B-0BE8-4836-A55B-3F95DD328E21@.microsoft.com...
>|||You can also do a combo. Like creating a global temptable and have your code
inserting into it.
Others can read using NOLOCK. But also insert into a table variable. The tab
le variable is used to
log after all is done.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Troy" <Troy@.discussions.microsoft.com> wrote in message
news:3D2C764E-F69F-42BA-81CB-114F157052E7@.microsoft.com...
> Hi Tabor,
> Thanks for your answer, that cleared it out. I must find another way to
> solve this....
> Cheers
>
> "Tibor Karaszi" wrote:
>|||hhmm, that could actually be someting to go for...
What about a traditional flat text file, any possibilities there?! Else i
might try the @.@.logtable solution and see who thats works out..
Cheers
"Tibor Karaszi" wrote:

> You can also do a combo. Like creating a global temptable and have your co
de inserting into it.
> Others can read using NOLOCK. But also insert into a table variable. The t
able variable is used to
> log after all is done.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Troy" <Troy@.discussions.microsoft.com> wrote in message
> news:3D2C764E-F69F-42BA-81CB-114F157052E7@.microsoft.com...
>|||Yes, file is an option, the problem is how you write to the file. Whichever
"hack" you use (like
xp_cmdshell and pipe something to a file etc), it will not be light-weight a
nd will slow down your
application.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Troy" <Troy@.discussions.microsoft.com> wrote in message
news:88692115-5C8A-4165-85DE-E46A85C489FB@.microsoft.com...
> hhmm, that could actually be someting to go for...
> What about a traditional flat text file, any possibilities there?! Else i
> might try the @.@.logtable solution and see who thats works out..
> Cheers
> "Tibor Karaszi" wrote:
>|||Put the data in a variable table, and save it after the transaction has
completed.
Variable tables by design are non-transactional.
AFAIK, normal tables and temp tables are both affected by transactions, as
are schema modifications etc.
eg.
begin trans
insert into tblBob values ( 'something' )
insert into @.myTable values ( 'done' )
rollback trans
insert into myRealTable SELECT value FROM @.myTable
"Troy" <Troy@.discussions.microsoft.com> wrote in message
news:2216FE6C-190B-40F8-8EA6-BC1621CF4EEE@.microsoft.com...
> Is there some way to bypass a transaction, when doing a insert. Meaning
when
> i'm inside a BEGIN TRANS and wants to do a INSERT that will not be removed
if
> a ROLLBACK is done, is there any way to do that?
> Example:
> BEGIN TRANSACTION
> INSERT log_table values ( 'Survive' )
> ROLLBACK TRANSACTION
> Afterwards the log_table contains the 'Survive' row...
> Cheers|||Of course, if you're doing a BULK INSERT - or something else which drops the
connection immidiately, it wont get to the end of the code to insert the
data from the Variable table
C'est la vie.
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:4343db0d$0$139$7b0f0fd3@.mistral.news.newnet.co.uk...
> Put the data in a variable table, and save it after the transaction has
> completed.
> Variable tables by design are non-transactional.
> AFAIK, normal tables and temp tables are both affected by transactions, as
> are schema modifications etc.
> eg.
> begin trans
> insert into tblBob values ( 'something' )
> insert into @.myTable values ( 'done' )
> rollback trans
> insert into myRealTable SELECT value FROM @.myTable
>
>
> "Troy" <Troy@.discussions.microsoft.com> wrote in message
> news:2216FE6C-190B-40F8-8EA6-BC1621CF4EEE@.microsoft.com...
> when
removed
> if
>