none
Error Handling in SQL Server 2000?

    Question

  • Hi All,

        I see that SQL Server 2000 handles error by using @@error only. I have a couple of staments and do I need to check them block by block?

        Here is my code:

    DECLARE @err INT
    BEGIN
    TRUNCATE Table dbo.A1
    TRUNCATE Table dbo.A2
    TRUNCATE Table dbo.A3
    TRUNCATE Table dbo.A4
    TRUNCATE Table dbo.A5
    TRUNCATE Table dbo.A6
    TRUNCATE Table dbo.A7
    TRUNCATE Table dbo.A8
    TRUNCATE Table dbo.A9
    TRUNCATE Table dbo.A10
    SELECT @err = @@error
    IF(@err<>0)
    BEGIN
     Print 'Error occurred'
    END
    END

    What if there occurs an error when truncating the Table A7? Does it still continue with the next steps? If the next steps succeed does it still catch the error at the end? If so, how to handle when there is a series of statements?

    Thanks

    Bangaaram


    Known is a DROP, Unknown is an OCEAN.

    Thursday, October 11, 2012 1:18 PM

Answers

  • Ok. But, doing so doesnt have any performance issues? Because, I have a number of INSERT and DELETE statements after these TRUNCATE statements. And for all those INSERT & DELETE Statements I have to capture the @@error block by block too. So, I was wondering if there is any other way where these things can be done easily.

    Yes, upgrade to a later version of SQL Server. :-)

    If you want the script to die on the first error, you can use the command

       SET XACT_ABORT ON

    However, on SQL 2000 XACT_ABORT ON does not affect compilation errors that occurs at run-time, for instance reference to non-existing tables. This is not so much an issue when you have a single script, since the script is aborted when the error occurs. But if you have nested stored procedures it can trip you.

    And you should always have a separate batch at the end with

    IF @@trancount > 0 ROLLBACK TRANSACTION

    Overall, error handling in SQL Server is extremely messy, and it was even messier in SQL 2000.

    But checking @@error after each statement is not a performance problem. But it's very tedious and litters the code.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, October 11, 2012 9:53 PM

All replies

  • This is an old URL that i had that talsk abount error handling in SQL SQL 2000 Error Handling.

    The @@Error logs only error numbers that are high in severity (i think the number is 10). if not it will not store any value. But if you want to handle errors in a better way, put the sql within transaction blocks and have the @@ error at the end of each statement.


    Regards, Dinesh

    Thursday, October 11, 2012 1:58 PM
  • This is an old URL that i had that talsk abount error handling in SQL SQL 2000 Error Handling.

    The @@Error logs only error numbers that are high in severity (i think the number is 10). if not it will not store any value. But if you want to handle errors in a better way, put the sql within transaction blocks and have the @@ error at the end of each statement.


    Regards, Dinesh


    I didn't get that clearly. So, do you suggest me to check for error after each truncation?

    Known is a DROP, Unknown is an OCEAN.

    Thursday, October 11, 2012 2:22 PM
  • Yes, you have to capture the @@error after every statement because it contains only the error of the last statement.

    If the error occurs on truncate no. 7, your variable will show only the error after truncate no. 10

    Thursday, October 11, 2012 2:51 PM
  • Yes as christa has explained, the @@ error logs details only for the last statement and only when the Severity of the error is greater than 10.

    Regards, Dinesh

    Thursday, October 11, 2012 2:55 PM
  • Yes, you have to capture the @@error after every statement because it contains only the error of the last statement.

    If the error occurs on truncate no. 7, your variable will show only the error after truncate no. 10

    Ok. But, doing so doesnt have any performance issues? Because, I have a number of INSERT and DELETE statements after these TRUNCATE statements. And for all those INSERT & DELETE Statements I have to capture the @@error block by block too. So, I was wondering if there is any other way where these things can be done easily. Please suggest me.


    Known is a DROP, Unknown is an OCEAN.

    Thursday, October 11, 2012 2:56 PM
  • Ok. But, doing so doesnt have any performance issues? Because, I have a number of INSERT and DELETE statements after these TRUNCATE statements. And for all those INSERT & DELETE Statements I have to capture the @@error block by block too. So, I was wondering if there is any other way where these things can be done easily.

    Yes, upgrade to a later version of SQL Server. :-)

    If you want the script to die on the first error, you can use the command

       SET XACT_ABORT ON

    However, on SQL 2000 XACT_ABORT ON does not affect compilation errors that occurs at run-time, for instance reference to non-existing tables. This is not so much an issue when you have a single script, since the script is aborted when the error occurs. But if you have nested stored procedures it can trip you.

    And you should always have a separate batch at the end with

    IF @@trancount > 0 ROLLBACK TRANSACTION

    Overall, error handling in SQL Server is extremely messy, and it was even messier in SQL 2000.

    But checking @@error after each statement is not a performance problem. But it's very tedious and litters the code.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, October 11, 2012 9:53 PM