July 8, 2010 at 1:09 pm
Hi All,
We are writting one script which calls many stored procedures inside it and these stored procedure has transaction but we are using single transaction in script. Is there any way to avoid internal transaction of stored procedure.
We can't change stored procedure as they are already in production environment otherwise I have thought of putting some condition to ignore transaction inside the procedure
July 8, 2010 at 5:11 pm
The transactions held inside the stored procedures should not matter unless they rollback. Each time you start a transaction, your @@TRANCOUNT increases. The transaction will only be committed when you reach a COMMIT TRAN when @@TRANCOUNT is 1 (and decreasing to 0).
However, if you hit a ROLLBACK, then your entire transaction will be rolled back, regardless of @@TRANCOUNT.
If the rollback occurs inside a stored procedure, then the outer calling proc will throw an error when it regains control: Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
This might be perfect for you - any error will cause your entire process to fail and rollback, while all internal commits will do nothing - but continue to work.
July 9, 2010 at 12:12 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply