Changing connection transactions to database transactions

Hi there,

I have decided to move all my transaction handling from asp.net to stored procedures in a SQL Server 2000 database. I know the database is capable of rolling back the transactions just likemyTransaction.Rollback() in asp.net.
But what about exceptions? In asp.net, I am used to doing the following:

<code>
Try
'execute commands
myTransaction.Commit()
Catch ex As Exception
Response.Write(ex.Message)
myTransaction.Rollback()
End Try
</code>
Will the database inform me of any exceptions (and their messages)? Do I need to put anything explicit in my stored procedure other thanrollback transaction?

Any help is greatly appreciated

Try these links for most of your needs, the most important is Transaction Save Point, it keeps your transactions from rolling back to the beginning if there is a roll back. If you need to use triggers look into Instead Of trigger it allows you to put your transactions within the trigger. Run a search for Transaction Save Points and Instead Of triggers in the BOL(books online) Hope this helps.

http://www.codeproject.com/database/sqlservertransactions.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_1ub2.asp

|||

Thanks Caddre,

I checked those links and finally figured it out. I have taken the approach to code my own error messages and put them in an output parameter.

One more thing, is there a way to simulate errors during a transaction? At the client side, I could explicitly throw an exception that would be caught. I don't know if this is possible in stored procedures... just want to test the error handling in my stored procedure. Anyone an idea?

|||Transaction stored procs are in a separate class, you either commit or roll back, that is the reason Savepoint is very important, if there is no Savepoint you can roll back to number one from number nineteen in a twenty transaction stored proc. You could test your Savepoints. Run a search on transaction error handling in the BOL(books online). Hope this helps.