September 14, 2009 at 6:20 am
I'm guessing this must be a common sort of problem, but I can't find the right way to search for it and get anything meaningful :crazy:, so appologies if its come up before.
I am working on a fairly old system that has an error logging procedure, used in this way:-
EXEC @Result = spu_MyProc @Param
IF (@Result <= 0)
EXEC spu_LogError 'spu_MyProc', @Result, @Param
[/code]
The spu_LogError proc simply adds another row to a log table with the details of the error.
In my new code I have to run 2 existing procedures - both must succeed or rollback and report the error.
[code]
DECLARE @ERR BIT
SET @ERR = 0
BEGIN TRAN
-- run first proc
EXEC @Result = spu_MyProc1 @Param
IF (@Result <= 0)
BEGIN
EXEC spu_LogError 'spu_MyProc1', @Result, @Param
SET @ERR = 1
END
ELSE
BEGIN
-- run 2nd proc only if first worked
EXEC @Result = spu_MyProc2 @Param
IF (@Result <= 0)
BEGIN
EXEC spu_LogError 'spu_MyProc2', @Result, @Param
SET @ERR = 1
END
END
IF @ERR = 1
ROLLBACK TRAN
ELSE
COMMIT TRAN
[/code]
Obviously if this rolls back, the errors logged by the spu_LogError procedure also get rolled back, and I have no details of the error to investigate.
I've tried the new BEGIN TRY/CATCH syntax, but that gives me another error entirely when one of the transactions fails (Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT) and It doesn;t help with preserving the contents of the log table.
September 14, 2009 at 6:42 am
Log your data to a table variable.
declare @table table
(
col1 integer)
begin transaction
insert into @table values(1)
rollback
select * from @table
September 14, 2009 at 6:54 am
Or... do the rollback as part of the error handling for each step:-
DECLARE @ERR BIT
SET @ERR = 0
BEGIN TRAN
-- run first proc
EXEC @Result = spu_MyProc1 @Param
IF (@Result <= 0)
BEGIN
ROLLBACK TRAN
EXEC spu_LogError 'spu_MyProc1', @Result, @Param
SET @ERR = 1
END
ELSE
BEGIN
-- run 2nd proc only if first worked
EXEC @Result = spu_MyProc2 @Param
IF (@Result <= 0)
BEGIN
ROLLBACK TRAN
EXEC spu_LogError 'spu_MyProc2', @Result, @Param
SET @ERR = 1
END
END
IF @ERR = 0
COMMIT TRAN
September 14, 2009 at 8:07 am
Thanks for your suggestions.
I've gone with Ian's for now, as its easiest.
I was in 'C' (think levels of brackets {}) programmer mindset and having the rollback not at the same level as the begin tran just did not compute. :doze:
Now its never as simple as you first thought, and the existing procedures spu_MyProc1, spu_MyProc2 also do some error logging, something like this.
CREATE PROC spu_MyProc1
@Param varchar(50)
AS
BEGIN
IF @Param = ''
BEGIN
EXEC spu_LogError 'spu_MyProc1: Invalid parameter', 0, @Param
RETURN -1;
END
IF EXISTS (SELECT * FROM dbo.ParamCheck WHERE Param=@Param)
BEGIN
EXEC spu_LogError 'spu_MyProc1: ParamCheck exists', 0, @Param
RETURN -2;
END
-- .... etc rest of procedure
END
So these errors are lost with the rollback.
What I'm thinking of doing (using Dave's suggestion) is outlined in pseudo-code below.
I'm posting this as a sanity check as it looks like a quite lot of changes to existing, tested and working code.
-- add a new column to the Logtable - somethinglike CallID as UNIQUEIDENTIFIDER
-- Generate a NEWID as value for CallID for use in any logging
-- Pass this CallID to all procedures as the final optional parameter (so as not to break the existing uses)
-- change spu_LogError to accept an (optional) Uniqueidentifier to put in the new column,
DECLARE @LOGTable TABLE ( -- blah blah)
DECLARE @ERR BIT
SET @ERR = 0
BEGIN TRAN
-- run first proc
EXEC @Result = spu_MyProc1 @Param, @CallID
IF (@Result <= 0)
BEGIN
-- preserve any error logging to the table variable before the rollback
INSERT INTO @LOGTable
SELECT * FROM LogTable WHERE CallID = @CallID;
ROLLBACK TRAN
INSERT INTO @LOGTable SELECT 'spu_MyProc1', @Result, @Param, @CallID;
SET @ERR = 1
END
--- ... etc rest of proc
IF (@ERR = 0)
COMMIT TRAN
ELSE
-- commit table variable to the permanent error log
INSERT INTO LogTable SELECT * FROM @LogTable;
I'd be grateful for any views on this method - workable or over-complex?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply