CODEWITHSUNDEEP
sql-serversql-server-2008stored-procedureserror-handling
fourdam
fourdam

Reputation: 126

SQL Server Stored Procedure Error Handling

I have a stored procedure which is runs automatically every morning in SQL Server 2008 R2, part of this stored procedure involves executing other stored procedures. The format can be summarised thus:

BEGIN TRY
  -- Various SQL Commands
  EXECUTE storedprocedure1
  EXECUTE storedprocedure2
  -- etc
END TRY

BEGIN CATCH
  --This logs the error to a table
  EXECUTE errortrappingprocedure
END CATCH

storedprocedure1 and storedprocedure2 basically truncate a table and select into it from another table. Something along the lines of:

BEGIN TRY
  TRUNCATE Table1

  INSERT INTO Table1 (A, B, C)
  SELECT A, B, C FROM MainTable
END TRY

BEGIN CATCH
  EXECUTE errortrappingprocedure
END CATCH

The error trapping procedure contains this:

INSERT INTO 
    [Internal].dbo.Error_Trapping
    (
        [Error_Number],
        [Error_Severity],
        [Error_State],
        [Error_Procedure],
        [Error_Line],
        [Error_Message],        
        [Error_DateTime]
    )
    (
    SELECT 
        ERROR_NUMBER(),
        ERROR_SEVERITY(),
        ERROR_STATE(),
        ERROR_PROCEDURE(),
        ERROR_LINE(),
        ERROR_MESSAGE(),
        GETDATE()
    )

99% of the time this works, however occasionally we will find that storedprocedure1 hasn't completed, with Table1 only being part populated. However no errors are logged in our error table. I've tested the error trapping procedure and it does work.

When I later run storedprocedure1 manually it completes fine. No data in the source table will have changed by this point so it's obviously not a problem with the data, something else has happened in that instant which has caused the procedure to fail. Is there a better way for me to log errors here, or somewhere else within the database I can look to try and find out why it is failing?

Upvotes: 2

Views: 2082

Answers (3)

Bogdan Bogdanov
Bogdan Bogdanov

Reputation: 1723

Try to use SET ARITHABORT (see link). It must ROLLBACK in your case. Also the answer of @Kartic seem reasonable.

I recommned also to read about implicit and explicit transactions - I think that this is your problem. You have several implicit transactions and when error happeneds you are in the middle of the job - so only part is rollbackеd and you have some data in that tables.

Upvotes: 1

Kartic
Kartic

Reputation: 2985

I am not sure if I understood you completely. Below code is too big for comment. So posting as an answer for your reference. If this is not what you want, I'll delete it.

Can we add transaction handling part as well.

DECLARE @err_msg NVARCHAR(MAX)

BEGIN TRY    
BEGIN TRAN

    -- Your code goes here

    COMMIT TRAN    

END TRY    

BEGIN CATCH 
    SET @err_msg = ERROR_MESSAGE()
    SET @err_msg = REPLACE(@err_msg, '''', '''''')
    ROLLBACK TRAN  
    -- Do something with @err_msg
END CATCH

Upvotes: 0

Mohsen
Mohsen

Reputation: 79

There are some type of Errors that TRY..CATCH block will not handle them, look here for more information https://technet.microsoft.com/en-us/library/ms179296(v=sql.105).aspx . for such Errors you should handle them in your application. also I think you might have transaction management problem in your application too.

Upvotes: 0

Related Questions