March 25, 2011 at 7:33 am
Hello,
I want to simulate a scenario like this.
begin trans tr1
begin trans tr2
-- do insert/update
commit trans tr2
-- do insert/update
rollback trans tr1
If need that rollback trans tr1 not rollback tr2.
Can I do that?
March 25, 2011 at 11:22 am
SAVE TRANSACTION will do what you need:
http://msdn.microsoft.com/en-us/library/ms188378.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 25, 2011 at 11:34 am
It doesn't work that way in SQL Server. nested transactions are a myth in SQL Server.
Check out this reference:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 25, 2011 at 11:35 am
Quick note, if you read that article by Paul Randal that I posted - he demonstrates the save transaction aspect as well. You will also see that even using a save transaction will not accomplish what you need since nested transactions don't work that way in SQL Server.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 25, 2011 at 11:52 am
My bad...I misread the question. SAVE TRANSACTION will let you save the inner work you've done provided you issue a COMMIT after you issue a ROLLBACK to a named savepoint...but if you ROLLBACK the outer transaction all work will be lost regardless of any SAVE TRANSACTION boundaries you have setup.
Paul's article is not trying to show that SAVE TRANSACTION does not work as described in BOL, just that it has some adverse effects on logging followed by a very nice PSA.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 25, 2011 at 11:57 am
Thanks for reply.
I tested save transaction for an hour an I cant get what I want.
I'll do a workaround using a old fox table that is not transactionable, and insert the record there.
I need the insert, no matter what happen with sql server,
If rollback, I'll have the record in the fox table.
I hope you understand my situation-
Thanks a lot.
March 25, 2011 at 12:16 pm
Yup - and that kind of workaround is not uncommon. If you read the comments in that blog article I posted, they demonstrate another workaround - look for the comment by Paul White.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 25, 2011 at 12:42 pm
Thanks right now i'm reading the article.
Thanks for help me!!!!
March 25, 2011 at 12:44 pm
NP - you're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 26, 2011 at 4:38 am
hi,
i have something interesting for you about the query you have asked.
-----------
March 27, 2011 at 6:30 am
khshsh (3/26/2011)
hi,
i have something interesting for you about the query you have asked.
-----------
Hi, please share what you have-
Thansks!
March 27, 2011 at 2:05 pm
gheinze (3/27/2011)
khshsh (3/26/2011)
hi,
i have something interesting for you about the query you have asked.
-----------
...
Hi, please share what you have-
Thansks!
It was just a link to a website that was unrelated.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply