Results 1 to 3 of 3

Thread: Error Handling

  1. #1
    Join Date
    Jan 2002
    Posts
    3

    Unanswered: Error Handling

    We are currently creating an Application using SQL Server 7 and I need to figure out a way for Error Handling for Back End process. There will be multiple jobs that will be run which are called by Visual Basic.
    I would need to create one procedure that should be called by all different Stored Procedures if an error Happens. I need to record that error to a table. What would be the best way of Error Handling. What options do I have?

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    What you are suggesting is not possible as it is possible to get errors (in fact quite likely with v7+) that are not trappable but return the error directly to the client.

    You are probably better off defining an error class in VB and calling that on all errors.

  3. #3
    Join Date
    Jun 2003
    Posts
    5
    I have implemented an errorhandling scheme with SQL 7.0 where the errormessages are stored in the master..sysmessages table and referred to by errorid. These errors can be passed between procedures with use of the RAISERROR() function and the master..sysmessages.error field is equal to the @@errorid of the RAISERROR() statement. The main limitation is that you can not use system errors, you have to code for all error cases and in the event that a system error occurs you have to substitute the user defined error in its place. The reason for this is that you can not reraise most system errors and the ones you can may or may not include variable substitution, which makes reraising the error impossible.

    Sample:
    IF (@variable IS NULL)
    BEGIN
    SET @ErrorID = xxxxx
    GOTO ErrorHandler
    END
    ---------------------
    INSERT INTO TABLE()
    VALUES()
    SELECT @ErrorID = @@error
    IF @ErrorID <> 0
    BEGIN
    IF @ErrorID > 50001
    BEGIN
    GOTO ErrorHandler
    END
    ELSE
    BEGIN
    SET @ErrorID = xxxxx
    GOTO ErrorHandler
    END
    END
    ---------------------
    Return 0
    ErrorHandler:
    IF @ErrorID <> 0
    BEGIN
    IF @ErrorID > 50001
    BEGIN
    RAISERROR(@ErrorID,16,1)
    RETURN
    END
    ELSE
    BEGIN
    SET @ErrorID = xxx
    RAISERROR(@ErrorID,16,1)
    RETURN
    END
    END

    Hope this helps,
    Ryan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •