Hi there,

We ran across the same situation - wanting to record use of a stored procedure, including its final status. Originally, we just added a line to the stored procedure that recorded its own execution in a log table, but that failed if the transaction rolled back for anything.

Now we have a "header" procedure that we run at the top of each stored procedure. We pass in the calling procedure's name. The header procedure uses osql to write a log entry, and returns an identity value that indicates the row created in the log table. That way, even if the SP fails, we have a record that it tried to run.

We also have a "footer" procedure that can take the identity value passed in from the header procedure and post the final status of the procedure's execution. Also uses osql so it doesn't rollback on failure.

Kimber