If say, you would be using an SP to update all 3 tables at once, you would not have this problem, but the question is how to pass parameters?

In SQL Server 2005 you may pass XML as a parameter, but I don't think it would ease the process.

E.g. if you somehow pass curUpdateTable1.XML (all updated fields for 1 table), same for the second and the third - I'm not sure how can you now handle it in SQL Server.

How can we pass updated values for the fields in this SP?

>Good evening,
>
>As I am converting my program from monolithic to a 3-tier I have across the following challenge.
>
>I have a routine where user enters a lot of data and a couple of different modules have to be updated. Each module has several tables. For example, user enters a work orders and enters labor and materials. Therefore 3 modules have to be updated: work order, labor, and materials. Each one has business and data tier objects. And each one will use TRANSACTION when updating tables of the module, in order to be able to revert changes if anything fails. The issue is how to make sure that if one module update fails other modules can be reverted? or what to do? Following the example above, say user says SAVE. Program updates work order module (via work order object), then labor module (via labor object), and then materials module (via materials object). But materials update fails. How do you recommend graceful revert or some way of getting back on the work order and labor module tables?
>
>Any suggestions would be greatly appreciated.