locked
Error Handler Email RRS feed

  • Question

  • Hi Experts ,


    We have numerous dynamic sql's embedded in the same store procedure . For error handling what we did if in case there  is error in any transaction all transactions are rolled back .

    Example : Like Below

    set @sql =
    'begin tran
    insert into xyztable

    SELECT * FROM table

    update XYZ

    SET Column1 = Value

    if @@Error != 0    Rollback tran else Commit Tran;'
    --select len(@sql),@sql
    exec(@sql)

    Like above the store proc contains several blocks of dynamic sql . The store proc  runs every 10 mins ..

    Am looking for a solution  valid transactions gets committed and failed transaction are sent as an email .

    Please advice .

    Thanks

    Priya

    Wednesday, January 7, 2015 6:13 PM

Answers

All replies

  • Instead of @@ERROR check add a try ..catch block and in the catch block add a call to sp_send_dbmail to sent the error details in a mail

    see example here

    http://databases.about.com/od/sqlserver/a/try_catch.htm

    http://www.codeproject.com/Tips/624780/Try-Catch-in-SQL-Reporting-stored-procedure-error


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Naomi N Wednesday, January 7, 2015 7:44 PM
    • Marked as answer by Priya Bange Friday, January 9, 2015 2:55 PM
    Wednesday, January 7, 2015 6:19 PM
  • Hi Visakh16 ,

    Thanks for the quick help .

    The below support link you provided is exactly what am looking for :

    http://databases.about.com/od/sqlserver/a/try_catch.htm

    Only Issue is can we also email back the record for which the insert fail that will help us to identify and easily troubleshoot the issue .

    Thanks a lot

    Priya

    Wednesday, January 7, 2015 6:42 PM
  • If you want to continue to use your existing logic, RAISEERROR might be of help:

    IF @@Error <>  '1'
    BEGIN
      PRINT 'Rollback tran'
      RAISERROR ('BOOOOOM!', 16, 1);
    END
    ELSE
    BEGIN
     PRINT 'Commit Tran'
    END

    Wednesday, January 7, 2015 6:43 PM
  • Hi Patrick ,

    This link really helps http://databases.about.com/od/sqlserver/a/try_catch.htm

    just looking for a way where i can also mail back the record that caused error will inserting . thank you

    Wednesday, January 7, 2015 6:47 PM
  • Why not divert the records instead? Put all your errors into a table, and inspect it periodically?
    Wednesday, January 7, 2015 8:47 PM
  • Or at least have a separate process to read the error records and send the mail. Don't put that in your current processing. I would guess it is messy already as it is.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, January 7, 2015 11:09 PM
  • Hi Thank you all for the advice..

    Is it possible to  move those records that  got failed while insertion . For those records i can schedule a timely email to the operation team .

    Thanks in Advance

    Priya

    Friday, January 9, 2015 8:06 AM
  • Using your existing logic:

    IF @@Error <>  '1'
    BEGIN
      PRINT 'Rollback tran'
      INSERT INTO badDataDump (dataColumn) VALUES (CAST(badDatacolumn AS VARCHAR))
    END
    ELSE
    BEGIN
     PRINT 'Commit Tran'
    END

    Friday, January 9, 2015 2:12 PM
  • Hi Patrick,

    Thanks for the reply i didn't tried out but wish to confirm on this

    1. The logic suggested above will insert the record into the baddatadump table with the record failed to insert.

    2. Other transactions won't be affected because of the failed transaction and they will be committed .

    Thanks

    Priya

    Friday, January 9, 2015 2:42 PM
  • Yes, you should really look into using TRY/CATCH loops:

    DECLARE @a TABLE (a VARCHAR(1))
    DECLARE @errors TABLE (badData VARCHAR(MAX))
    
    DECLARE @value VARCHAR(20) = '1231212312123' 
    
    BEGIN TRY
         INSERT INTO @a (a) VALUES (@value)
    END TRY
    BEGIN CATCH
         INSERT INTO @errors (badData) VALUES ('VALUE: ' + @value + ' - ' + ERROR_MESSAGE())
    END CATCH
    
    
    SELECT *
      FROM @errors

    • Marked as answer by Priya Bange Friday, January 9, 2015 2:53 PM
    Friday, January 9, 2015 2:50 PM
  • Thank You so much  !!!

    Exactly i was looking for such a solution . You made my day .

    Thank you take care

    Friday, January 9, 2015 2:53 PM