Commit Nested Transaction

How do I commit a nested transaction while rolling back the outer transaction
BEGIN MASTER TRANSACTION
Action 1
BEGIN NESTED TRANSACTION
Action2
COMMIT NESTED TRANSACTION
ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave action
2 committed.)You can't. If you roll back an outer transaction all the nested ones roll
back as well. If you used a SAVE TRAN you can roll it back but any work
done inside that (even nested trans ) are rolled back as well. If you issue
a plain ROLLBACK (not associated with a nested tran) then everything is
rolled back.
--
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
> How do I commit a nested transaction while rolling back the outer
> transaction
> BEGIN MASTER TRANSACTION
> Action 1
> BEGIN NESTED TRANSACTION
> Action2
> COMMIT NESTED TRANSACTION
> ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave
> action
> 2 committed.)|||What about if one of the action's is a call to another stored procedure. The
action of that call will be rolled back right? Or is just if the action is a
statement in the same procedure?
"Andrew J. Kelly" wrote:
> You can't. If you roll back an outer transaction all the nested ones roll
> back as well. If you used a SAVE TRAN you can roll it back but any work
> done inside that (even nested trans ) are rolled back as well. If you issue
> a plain ROLLBACK (not associated with a nested tran) then everything is
> rolled back.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
> > How do I commit a nested transaction while rolling back the outer
> > transaction
> >
> > BEGIN MASTER TRANSACTION
> >
> > Action 1
> >
> > BEGIN NESTED TRANSACTION
> > Action2
> > COMMIT NESTED TRANSACTION
> >
> > ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave
> > action
> > 2 committed.)
>
>|||On Fri, 2 Sep 2005 13:37:02 -0700, Scott wrote:
>What about if one of the action's is a call to another stored procedure. The
>action of that call will be rolled back right? Or is just if the action is a
>statement in the same procedure?
Hi Scott,
EVERYTHING in a transaction is rolled back on a ROLLBACK. That's the
only way to satsify the "A" in the ACID properties. From Books Online:
>>A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, called the ACID (Atomicity, Consistency, Isolation, and Durability) properties, to qualify as a transaction:
>>Atomicity
>>A transaction must be an atomic unit of work; either all of its data modifications are performed, or none of them is performed.
(snip)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Anything you do after a Begin Tran is governed by that. It doesn't matter
if the statements are in called sp's or not.
--
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:BCCFD268-AB8C-4158-8A47-169C4F57C912@.microsoft.com...
> What about if one of the action's is a call to another stored procedure.
> The
> action of that call will be rolled back right? Or is just if the action
> is a
> statement in the same procedure?
> "Andrew J. Kelly" wrote:
>> You can't. If you roll back an outer transaction all the nested ones
>> roll
>> back as well. If you used a SAVE TRAN you can roll it back but any work
>> done inside that (even nested trans ) are rolled back as well. If you
>> issue
>> a plain ROLLBACK (not associated with a nested tran) then everything is
>> rolled back.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
>> > How do I commit a nested transaction while rolling back the outer
>> > transaction
>> >
>> > BEGIN MASTER TRANSACTION
>> >
>> > Action 1
>> >
>> > BEGIN NESTED TRANSACTION
>> > Action2
>> > COMMIT NESTED TRANSACTION
>> >
>> > ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave
>> > action
>> > 2 committed.)
>>

Command line action still not supported after installing sp1

Why is the command line action still not supported after installing SP1 in the SQL Server Business Intelligence Development Studio?

What command line functionality you are looking for from BI Dev Studio?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

sorry my mistake, I mean the command line actions in the BI Dev Studio!

I know there is a workaround, but I expect a command line action in the action listbox in the BI Dev Studio.

|||

hello Peter,

This is actually as expected: this action type is not listed in the UI intentionally. Actions of this type are unsafe, so they are explicetly 'demoted' in the UI. However, if you already have a command line action (like in the migrated cube), you should still be able to see them with the proper type.

hope this clarifies