Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2007
    Posts
    9

    Unanswered: PLEASE HELP!!! multiple error messages

    I have a SS2K5 stored procedure that executes 2 others stored procedures
    sp_zero1 and sp_zero2
    sp_zero1 and sp_zero2 do the same thing ... raises an Divide by zero error
    I need to (print / select into a database) both error messages using just one
    try catch block instead of 2 blocks like in the next example:


    -- THIS IS THE WORKING CODE THAT I DONT WANT
    BEGIN TRY
    exec sp_zero1
    END TRY
    BEGIN CATCH
    PRINT ERROR_MESSAGE()
    PRINT ERROR_PROCEDURE()
    END CATCH
    BEGIN TRY
    exec sp_zero2
    END TRY
    BEGIN CATCH
    print ERROR_MESSAGE()
    print ERROR_PROCEDURE()
    END CATCH



    if I try the next code

    --THIS IS THE NON WORKING CODE
    BEGIN TRY
    exec sp_zero1
    exec sp_zero2
    END TRY
    BEGIN CATCH
    print ERROR_MESSAGE()
    print ERROR_PROCEDURE()
    END CATCH

    only the first error message is printed and the execution is stopped

    This is a generic example ... in reality I have a stored procedure that executes tens and hundreds of other stored procedures ... so thats the reason I need just one block of try catch instead of hundreds of blocks

    thank you

  2. #2
    Join Date
    Dec 2007
    Posts
    9

    Re: PLEASE HELP!!! multiple error messages in SS2K5

    I dont need exclusively try catch blocks ... I just want to store into a table the error messages no matter how are inserted there

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You would need to do this in a loop (probably using dynamic SQL) to do this. There would be an alternative using @@ERROR and GOTO statements (effectively spoofing something more like VB error handling) but this would require similar code to multiple TYRY.. CATCH blocks. I would say that if you need to call hundreds of procs and need to catch each and every error then you need hundreds of TRY... CATCH blocks unless the above ideas sound appealing.

    HTH

  4. #4
    Join Date
    Dec 2007
    Posts
    9

    PLEASE HELP!!! multiple error messages in SS2K5

    ok ... i still dont like the solution with loops and gotos so ... does anyone knows where SQL SERVER puts the error messages (the ones that appears in the Results window)

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It doesn't put them anywhere. It reqtrieves them from the system table and outputs them to the client. Any logging (apart from if, for example, you ran this from a job) has to be done by your good self.

    Another option (though I doubt you would like this either) is to have error trapping within the hundreds of procedures that you call rather than in the calling procedure.

  6. #6
    Join Date
    Jul 2007
    Posts
    96
    If the first fails to execute, thus raising an error, why bother to run the others? Either the whole procedure executes or it will halt on the error. If the first failed then you can assume that all others will not be executed therefore raising a "manual" error for each and every single one of them stating that they weren't executed due to the error executing the previous call. Confusing? Maybe

    Could you be more specific about what you wish to acomplish?

  7. #7
    Join Date
    Dec 2007
    Posts
    9
    ok ... i did a 'customized' replication using stored procedures ... so I have a server and many linked servers from where I send and receive data thru this replication
    What I need is to trap eventual errors that appear in this replication and store them into a table ... Can anyone help me please?

Posting Permissions

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