Multi-Step Transaction, fails but reports success

I have for example the following SQL:
BEGIN TRAN
Delete from users where userid = 102
Update mainSettings set userCnt = UserCnt -1
COMMIT TRAN
If for example the "update" statement fails, but the "delete" statement
is successful - the overall transaction reports no error, and thus, my
appliction thinks everything was a success.
Any ideas?
VB 6.0
MDAC ADO 2.7
JasonAnswered in microsoft.public.sqlserver.programming
Please do not multi-post.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<jroozee@.gmail.com> wrote in message
news:1112042240.645733.295030@.f14g2000cwb.googlegroups.com...
> I have for example the following SQL:
> BEGIN TRAN
> Delete from users where userid = 102
> Update mainSettings set userCnt = UserCnt -1
> COMMIT TRAN
>
> If for example the "update" statement fails, but the "delete" statement
> is successful - the overall transaction reports no error, and thus, my
> appliction thinks everything was a success.
> Any ideas?
> VB 6.0
> MDAC ADO 2.7
> Jason
>|||You have to check for @.@.error after every statement that insert / delete or
update
the db. Also, you have to check the return value of the sp.
create procedure proc1
@.userid
as
set nocount on
declare @.error int
BEGIN TRAN
Delete from users where userid = @.userid
set @.error = @.@.error
if @.error != 0
begin
rollback transaction
raiserror('Error deleting from table users.', 16, 1)
return 1
end
Update mainSettings set userCnt = UserCnt -1
if @.error != 0
begin
rollback transaction
raiserror('Error updating from table [mainSettings].', 16, 1)
return 1
end
COMMIT TRAN
return @.@.error
go
declare @.rv int
declare @.error int
declare @.tc int
set @.tc = @.@.trancount
exec @.rv = proc1 102
set @.error = coalesce(nullif(@.rv, 0), @.@.error)
if @.error != 0
begin
print 'there was an error.'
end
if @.@.trancount != @.tc
rollback transaction
go
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server â' a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"jroozee@.gmail.com" wrote:
> I have for example the following SQL:
> BEGIN TRAN
> Delete from users where userid = 102
> Update mainSettings set userCnt = UserCnt -1
> COMMIT TRAN
>
> If for example the "update" statement fails, but the "delete" statement
> is successful - the overall transaction reports no error, and thus, my
> appliction thinks everything was a success.
> Any ideas?
> VB 6.0
> MDAC ADO 2.7
> Jason
>|||Ok, forget the BEGIN/COMMIT...Even with out that, why wouldnt SQL
server return a error?
Jason|||Read the articles. There you will find the answer.
AMB
"jroozee@.gmail.com" wrote:
> Ok, forget the BEGIN/COMMIT...Even with out that, why wouldnt SQL
> server return a error?
> Jason
>|||Same reason, last statement in the batch was successful.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<jroozee@.gmail.com> wrote in message
news:1112043813.998362.180420@.g14g2000cwa.googlegroups.com...
> Ok, forget the BEGIN/COMMIT...Even with out that, why wouldnt SQL
> server return a error?
> Jason
>|||Additionally, the reason you must check for the error, is that the SQL
programmers can not make the decision for you as to whether or not the error
is sufficient to roll back the transaction. That is for you to decide, so
you much catch the errors and rollback the transaction when necessary.. (
There are types of errors which will automatically roll back the transaction
without your intervention. These are generally more severe errors which
cause batch abort, or you have been chosen as a deadlock victim.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<jroozee@.gmail.com> wrote in message
news:1112042240.645733.295030@.f14g2000cwb.googlegroups.com...
>I have for example the following SQL:
> BEGIN TRAN
> Delete from users where userid = 102
> Update mainSettings set userCnt = UserCnt -1
> COMMIT TRAN
>
> If for example the "update" statement fails, but the "delete" statement
> is successful - the overall transaction reports no error, and thus, my
> appliction thinks everything was a success.
> Any ideas?
> VB 6.0
> MDAC ADO 2.7
> Jason
>

Multiple Stored Procedures and Transactions

I have a simple question: Can I have two or more stored procedures that begin transactions but the last stored procedure will commit all transactions if there was no error?

In other words, I have a stored procedure that performs some legacy stuff. I do not want to rewrite that logice, execpt I am putting it into a Stored Procedure since it currently is embedded SQL.

To support newer functionality, I am writing data to additional new tables. However, I do not want the *new* things to occur if there is an error. This is easy enough, I dont call the Stored Procedure for the new functionality if there was an error. However, if there was not an error and the newer stored procedure is called AND there is an error in the new stored procedure, I want to ROLLBACK the changes from the proceeding stored procedures.

To my understanding, I can name transactions but that is only to indicate in the logs what transactions have failed.

I thought about not using transactions for any of the individual stored procedures and calling them from a main stored procedure. The main stored procedure will have a BEGIN TRY and an END TRY (I am using SQL Server 2005) and at the top (right after the try) I will have a BEGIN TRANSACTION. In the BEGIN CATCH I will have a ROLLBACK and at the end I will have a COMMIT. If any of the stored procedures fail at any point it will cause the catch to occur thus rolling back all of my transactions. This would be the easiest way but I still need to deal with the question of, "What happens if any single stored procedure is called?" I guess I could have try and catches in each individual stored procedure for that.

I just want to make sure I am not overlooking something simple. I think I have the right idea by doing it this way I mentioned above. Because this is critical billing processing I just want to have some reassurance that this would work.

Thank

Why don't you add a column to the legacy output to indicate errors. That way nothing is stopped, but you can determine at what point errors start and output to the user, or react accordingly.

|||

Thank you for your response but the problem with that is I want things to stop, I dont want the data in both tables to be out of sync. The only reason we are supporting the older table is the web application is quite huge, very very large and at this point, rewriting legacy code (using classic ASP) to use our new tables would be time consuming. Additionaly, we have several apps wrote in Visual Basic 6 that would need to be rewrote as well to support our newer system. I am developing a new Payment Processing System in C# that writes to the database, and caputres additional data that is useful instead of waiting for end of month. The old system is very very inefficient but until we have the resources to go back and recode it, End of Month Reports still run off of it. These jobs take 2 hours to run (Yeah lots of data) However, the purpose of the new system is to capture some of that data that wasn't avaialble until the end of month. It does this by writing the data that one department needs concering payments and adjustments at the time that action is performed.

However, we cannot have any one table having a different value than the other. If I do it the way you stated then if there is an error, our historical end of month data will be different than data in our new table.

From my understanding, if I wrap everything in a TRY CATCH block in sql server and if any one statment or procedure fails and I am raising the error, the Outermost TRY CATCH BLOCK will also fail, thus braching off to its CATCH BLOCK and inside that CATCH Block I have a rollback. From what I have read, the outermost COMMIT and ROLLBACK are what matters and if it ROLLSBACK then everything rolls back.. I just want to make sure I am understanding this correctly.

Thanks!

John

|||

jgilmore:

Can I have two or more stored procedures that begin transactions but the last stored procedure will commit all transactions if there was no error?

Yes. In fact, that's the default behavior of "nested transactions"

Seehttp://www.codeproject.com/KB/database/sqlservertransactions.aspx and scroll down to "nested transactions" but here's the nutshell answer. Assuming proc1 executes proc2, which contains a begin tran/commit, as long as proc1 wraps the call to proc2 in another begin tran/commit/rollback, a rollback done as part of the outer transaction will in fact roll back the transaction done by proc2, even if proc2's transaction has commited.

For many more articles, just google "sql server nested transaction"

|||

Thank you dbland07666!

I wanted to ensure that this worked as I expected it to.

John