I need to do an "Insert" excluded of a ROLLBACK, is possible?

Question

I need to do an "Insert" excluded of a ROLLBACK, is possible?

Hector Angello Silva Carmona

SSCrazy

Points: 2720
More actions
May 18, 2005 at 9:32 am

#66427

Masters i need to do an "insert" excluded of a rollback
example :

begin tran
insert into table1 values ('a')
insert into table2 values ('b') (no rollback)
rollback
---------------------------------------------------------------------------
select * from table1
"nothing"
select * from table2
b

( i want that the value b was excluded of the rollback, is possible???
i can not tu put the insert into table2 outside of it&nbsp
Thanks masters

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

If the insert in table 2 is inside the transaction, there is no way to keep just that ('b') from not being rollbacked, as you have described.

When you do a rollback, everything unto the outermost transaction is rolled back (or to a savepoint). This is what transactions are all about - it's all or nothing. You can't just have some or something.

Please read up on the articles in BOL (Books on Line) about transactions and transactionhandling. There are tons of very good information in there, and it's very important to have a full understanding about how transactions work in SQL Server.

/Kenneth

Hector Angello Silva Carmona SSCrazy Points: 2720 More actions · Answer 2

mmm is posibble if the table table2 would be in another server?

using a distributed transaction ???

for example

begin tran

insert into table1 values('a')

use server.base

insert table2 values('B')

rollback

the table2 will be affected with the rollback?????

if i use a trigger in another table? the trigger is rollbacked too??

thanks ...

ron k SSCertifiable Points: 6103 More actions · Answer 3

This should work:

Declare @temp table (col_a char)

begin tran

insert into table1 values ('a')

insert into @temp values ('b') (no rollback)

rollback

insert into table2

select col_a from @temp

The table variable is created and held in memory and remains available after the rollback.

Hector Angello Silva Carmona SSCrazy Points: 2720 More actions · Answer 4

Great you are a giant, but is possible to recover the values of another sesion??? thanks

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

Hmmm.. Perhaps you could please explain what your problem really is? I mean, why are you trying the hardest to go around the very thing that databases are all about - making sure that your data is always in a consistent state?

/Kenneth

ron k SSCertifiable Points: 6103 More actions · Answer 6

I have to agree with kenneth. I really feel that whatever it is you are trying to do probably needs to be restructured so as to have a seemingly more "logical" flow.

JSBBS Valued Member Points: 65 More actions · Answer 7

I understand what Hector is trying to do... I've encountered the same problem and used the very same approach to work around it.

The inserts into table2 are used to audit/trace how the entire process behaved with the inserts into table1, regardless whether some business rule or SQL step fails while inserting into table1.

A suggestion for SQLServer2010 ... the concept of an autonomous transaction ( this is an Oracle concept ). If a transaction is autonomous, it is totally independant of any parent transaction.

Thoughts?

Hector Angello Silva Carmona SSCrazy Points: 2720 More actions · Answer 8

Ok it is a large history... well have you heard a system of CRM called Onyx?

well occurs that this system is made with a flow of sp encrypted, the encrypted sp eventually call a customizer sp. they can be touched but , i need to do a parsing of entery datas, these data are passed like parameters into de encrypted sp,some data is wrong when it is transformed into the flow,and it casts a error, i thought to do a log table, but there is a commit tran in the encrypted sp, and when occurs the error it does a rollback and no datas have been stored into the log table..

well my problem consists that my company doent allow to unencrypt the encrypted sp, because the support of resource is lost...

now i thought to do a txt file like log called by isql, well is the best solution that i found.. thank you a everybody...

Hector Angello Silva Carmona SSCrazy Points: 2720 More actions · Answer 9

Ok it is a large history... well have you heard a system of CRM called Onyx?

well occurs that this system is made with a flow of sp encrypted, the encrypted sp eventually call a customizer sp. they can be touched but , i need to do a parsing of entery datas, these data are passed like parameters into de encrypted sp,some data is wrong when it is transformed into the flow,and it casts a error, i thought to do a log table, but there is a commit tran in the encrypted sp, and when occurs the error it does a rollback and no datas have been stored into the log table..

well my problem consists that my company doent allow to unencrypt the encrypted sp, because the support of resource is lost...

now i thought to do a txt file like log called by isql, well is the best solution that i found.. thank you a everybody... sorry my bad english