We perform ETL on a table of transactions. After loading the transaction table, we want to use Stored Procedures (SPs) to perform the very complex business validations, and then to apply the transactions against a dozen permanent state tables. The nature of the data requires sequential processing of the transactions since some may be dependent on others or affect the results of others. Fortunately the data volume is very small.
As part of this effort, a small web app is being built to enable some manual cleanup of transactions which fail validation. We want it to call the same stored procedures to validate and apply the changes that the ETL does. This will ensure consistency and prevent duplicate coding. Validation and processing of a single trasnaction will span multiple procedures, and we need to have complete control over the rollback/commit activity. ANSI mode appears to offer this, but does anyone have any experience or suggestions on doing this? Several times I have seen "do not use ANSI mode" when calling procedures (especially from .Net) so I am hoping for some guidance. We can't afford to waste weeks of effort only to find out this approach isn't feasible.
Any thoughts would be appreciated.