Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: Plumbing the error stack

    Just a quickie - I think the answer is "No".

    In short: Is it possible to work your way through the error stack in a CATCH block?

    In long: A single statement can generate more than one error message. Unhandled (outside of a TRY...CATCH) we see all the messages. Within a CATCH block we can see only the last one (which often says something like "could not perform operation - see previous messages"). Any way we can see these previous messages in the CATCH block?

    Chars!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Please post sample code of your problem. See the sticky at the top of the thread, of you have no idea what that means ;-).


    Of course, I assume you are collecting the error messages from the new(ish) system variables ERROR_MESSAGE, ERROR_NUMBER, et al.?

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yah - it's the ERROR_MESSAGE() function family I am looking at.

    This is illustrative - I don't want any crap about "use the correct names in the first place you monkey".
    Code:
    USE test
    GO
    
    IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.foo')) 
    BEGIN
        DROP TABLE dbo.foo
    END
        
    CREATE TABLE dbo.foo
    (
          bar INT IDENTITY(1, 1) NOT NULL
        , CONSTRAINT pugh PRIMARY KEY CLUSTERED (bar)
    )
    
    BEGIN TRY
    
        ALTER TABLE dbo.foo
        DROP CONSTRAINT puhg
        
    END TRY
    BEGIN CATCH
    
        PRINT   ERROR_MESSAGE()
        
    END CATCH
    There is a prior error message to one that is returned by the function:
    Code:
    'puhg' is not a constraint.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Hmmm....An interesting problem. I think this can be done, if the drop constraint statement is issued by a vbscript or .NET program, because there is an error stack for those. It rather looks like the try-catch implemented for T-SQL is intended to help you recover from DML problems, rather than DDL ones.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - assuming that MS are consistent then a quick noodle in the messages table shows there aren't actually very many errors of this type:
    Code:
    SELECT  *
    FROM    sys.messages
    WHERE   messages.language_id    = 1033
        AND messages.text           LIKE '%see previous%'
    The XML ones sound DMLy, but I don't XML on principle

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah, building a .NET harness for testing deployment script in the event there was a previous error is gilding the lilly a bit. Thanks fort he idea - I'll stick with verbose success logging to go with my error traps

Posting Permissions

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