SQL Server Performance Forum – Threads Archive

error handling / jobs

Hello The error handling in transact SQL has always been a bit of a mystery to me, but I managed to make some code like: CREATE PROCEDURE SPRC_MAIN
AS
…
SET XACT_ABORT OFF
…
WHILE …
EXEC @retval = @procedure …
— If an error code or a value is returned from the SP,
— it indicates something went wrong.
— The function below returns the first non null/zero value
SET @retval = dbo.FN_INT_ISNULLORZERO(@@ERROR, @retval)
IF(@retval <> 0)
BEGIN
INSERT INTO errlog …
…
END
…
END
…
GO
In short, the procedure executes a number of procedures, if anything fails in the called sp’s, it’s logged in the table errlog The called procedures looks something like:
CREATE PROCEDURE SPRC_SUB1
AS
…
SET XACT_ABORT OFF
…
INSERT …
SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT
IF(@error <> 0) GOTO ERROR_HANDLER
…
RETURN(0) ERROR_HANDLER:
RETURN @error
GO Now, this all works fine if i execute SPRC_MAIN from Query Analyzer. If there are errors like duplicate key violation in one of the sub procedures, the error is logged into the errlog-table. However, if i run this procedure from a JOB, an error will just cause the procedure to halt and no errors are logged. Anyone knows why? cheers /Linus —
http://anticAPSLOCK.com

what will happen to the job if you remove error handling —————————————-
http://spaces.msn.com/members/dineshasanka

Im not sure what you mean, if i have my error handling code there when i run it as a job errors aren’t handled. If i remove the error handling it wont work neither..? just to make things clear: Run procedure from Query Analyzer -> errors are trapped and reported accordingly in my procedure.
Run procedure as job -> errors are not trapped, instead the job is halted and an error appear in the job history. The problem i have is that even if one procedure fails, i want the other ones to proceed and in the end of the main sp i generate a mail with information about which procedures failed and which did not.
But now instead Ill either get a mail that everything worked, or no mail at all :/ /L —
http://anticAPSLOCK.com

On a job step, you can set options on how to proceed on failure, "report success", "report failure" or "go to next step".

oh, thats what you meant <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> On the last step of a job you have to have either ‘quit with success’ or ‘quit with failure’, so ill try to set ‘quit with success’ also on ‘on failure’ and see if it helps. thanks for the suggestion! /L – http://anticAPSLOCK.com</a>

Well, you can set it on each individual step of the job. You must select "go to next step" to continue with any further steps in the job.

Also see if this helps
http://www.sommarskog.se/error-handling-I.html Madhivanan Failing to plan is Planning to fail

Hello! Thanks for the tips both of you. After a little testing with the errorhandling of the job I realized that wasnt it neither. After adding more debug code to my procedures and examining the job outputs I realized there was something else at work here… If I at a certain point in the code added a ‘SELECT 1’, the error handling worked as expected also when run as a job… This must’ve been due to a bug or misbehaviour of SQL server, very strange… The ‘SELECT 1’ seemed to have to be just before the ‘FETCH NEXT’ of the cursor that executed the procedures. Why is beyond me … cheers /Linus —
http://anticAPSLOCK.com

Well, you didn’t give any details about a cursor, so we have no idea either.[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

]]>