Post reply

Job fails due to an error which is being handled by the script being called

  • November 8, 2005 at 3:41 am

    #110692

    I have a script which loads data from an inbound/interface/holding table to the active tables. If errors occur I trap them individually, log which ones error, and continue the import.

    This works fine when calling the script from sql query analyser or osql but not when you call the procedure directly from a job.

    The job registers that an error has occurred even though it's been handled and stops dead.

    Any idea why this difference exists.

    I appreciate there is a work around using xp_cmdExec calling the procedure through osql, But would like to know the reason for the job failing when calling the procedure directly

  • November 9, 2005 at 11:16 am

    #603084

    Could you post the error text from the 'trapped' error ?

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • November 10, 2005 at 2:49 am

    #603177

    The error message is below.

    I understand what is causing the error in the first place, ie violation of a foregin key constraint.

    The issue is that if such an error occurs as I loop through the rows being inserted, I just want to record that the row in question has failed and continue with remaining rows.

    Thanks for you response

    KS

    Executed as user: VIRTUAL\sql.svclive. INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'PIMS_PROP_RESP_PE_FK'. The conflict occurred in database 'OCC_PORTAL_DEV', table 'PIMS_EMPLOYEE', column 'EMP_REF'. [SQLSTATE 23000] (Error 547)  The statement has been terminated. [SQLSTATE 01000] (Error 3621)  Associated statement is not prepared [SQLSTATE HY007] (Error 0)  INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'PIMS_PROP_RESP_PE_FK'. The conflict occurred in database 'OCC_PORTAL_DEV', table 'PIMS_EMPLOYEE', column 'EMP_REF'. [SQLSTATE 23000] (Error 547)  The statement has been terminated. [SQLSTATE 01000] (Error 3621).  The step failed.

  • November 14, 2005 at 1:30 pm

    #603747

    Unfortunately I do not have a solution. I've always been able to have a script behave in the same way in QA as in a Job/Scheduled task. The only solution may be to create a stored procedure and possibly manipulate the 'RETURN' values.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply