SSIS and Sql Server Journey

Personal Notes of Sarabjit Singh and Bhavpreet Singh

Back To Basic: Nested Transaction

on April 9, 2013

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/

Point to remember:

ROLLBACK will do two things that’s important to be aware of…
1) It will rollback all work back to the *first* or *outermost* BEGIN TRAN
2) It will also *reset* @@TRANCOUNT to zero (no matter the previous value)

Recall getting the error below when you forget to use @@Transcount>0 before rollback 

“Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.”

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: