Stored procedure rollback

[Brent Mondoux]

Hey everyone,

I have a set of stored procedures that call each other to perform a
set of tasks. I have decided to do this for database performance
reasons rather than doing it from a ColdFusion page (and to prevent
timeouts, etc.)

These stored procedures run every night and handle scheduled tasks in
a complex system.

An example of this would be:
sp_ExecuteScheduledTasks @ForDate
(calls) sp_UpdateEmployeeSalary @EmployeeID, @NewSalary,
@EffectiveDate
(then calls) sp_UpdateEmployeeCoverage @EmployeeID,
@PlanEnrollmentYear
(calls) sp_UpdateEmployeeBenefitCoverageandCosts @EmployeeID,
@PlanEnrollmentYear

This is a quickly drafted example of the complexity of this stored
proc routine.

The problem I have is that each time an error occurs in this stored
proc routine, I would like to save it to a table in the database for
future analysis and rectification the following day. The reason this
does not happen is because of the nested transactions. If the
transaction is going to fail, it also rolls back transactions within
itself (it's children transactions).

I've put together a very basic set of SQL statements below in order to
test and try to find a solution to my problem but have not yet
succeeded.

/**************************/
/* SQL Example Start */
/**************************/

BEGIN TRAN tran_with_error

INSERT INTO tblThisCausesAnError(error) VALUES('This causes an error')

BEGIN TRAN save_error_message
INSERT INTO tblErrorLog(message) VALUES ('This reports an error')

COMMIT TRAN save_error_message
/* SAVE TRAN save_error_message */

/* At this point an error has occured, rollback the transaction */
ROLLBACK TRAN tran_with_error

/**************************/
/* SQL Example End */
/**************************/

I have tried committing/saving the transaction, I have tried
disallowing delete access to the tblErrorLog table to public and the
SQL user that executes the process, I have even tried a delete trigger
that disallows any deletion but I realize that transactions are
handled differently and that a rollback is not considered a deletion.

Any ideas?

Thanks.

Brent