locked
Get the Identity Column value when the update statement fails. RRS feed

  • Question

  • Hi All,

    My current proc updates(updates using joins of two or three tables) millions of records as per the condition provided for each department.
    However, when the proc fails it writes to a ErrorTable, ERROR_MESSAGE(), ERROR_SEVERITY() and which department has failed.
    Since the records are updated keeping the selected departments in loop, I get the department in a temp variable.

    Now, I was asked to log the specific record where the failure was occured.
    Something like log the identity column value or primary key value which record has failed.

    Can someone help me with this issue?

    Thank you.

    Wednesday, September 9, 2015 4:39 PM

Answers

  • Basically there is no way.  Your UPDATE statement runs as a single statement.  All the try/catch can do is catch that statement error.  However, there is often a clue in the error message which indicates the problem which you can research.

    For example, "error converting varchar to datetime".  You need to look for where you are doing that and resolve any conversion errors before the UPDATE or use a CASE statement on the update or WHERE clause to avoid the issue.

    • Proposed as answer by Naomi N Wednesday, September 9, 2015 6:58 PM
    • Marked as answer by Eric__Zhang Monday, September 21, 2015 8:53 AM
    Wednesday, September 9, 2015 4:49 PM

All replies

  • Since you're doing this in a loop, you can assign the primary key ID of the record to a variable before you process it. Then, in a TRY..CATCH block you can write it to the error log along with the other error information, if I'm understanding your question correctly about the loop.

    Would this work for you?

    Thanks

    Carl




    • Edited by CarlGanz Wednesday, September 9, 2015 4:52 PM
    Wednesday, September 9, 2015 4:46 PM
  • Basically there is no way.  Your UPDATE statement runs as a single statement.  All the try/catch can do is catch that statement error.  However, there is often a clue in the error message which indicates the problem which you can research.

    For example, "error converting varchar to datetime".  You need to look for where you are doing that and resolve any conversion errors before the UPDATE or use a CASE statement on the update or WHERE clause to avoid the issue.

    • Proposed as answer by Naomi N Wednesday, September 9, 2015 6:58 PM
    • Marked as answer by Eric__Zhang Monday, September 21, 2015 8:53 AM
    Wednesday, September 9, 2015 4:49 PM