Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    193

    Unanswered: Trapping Error Messages before Rollback

    Hi,

    I am using SQL Server 2k. I have a situation where I have several stored procedures and I wish to be notified when there is an error w/ a given statement within a procedure. I wish to apply this concept to all the procedures. I am a bit new to error handling. I understand the concept of @@Error for the most part. I would like to run a procedure that can have say 15 statements, they are inserts, updates, create temp tables, etc, capture any errors and rollback if those errors exist. I am to understand however that anything between the begin transaction and the rollback transaction commands will be rolled back including triggers and any method of capturing the errors so I am a bit stuck. I wanted to get the error, and the statement number, as I have numbered all the statements in the procedures, and insert them into an error report table. I wish to rollback the procedure upon failure so I can go back and run the statements step by step in an effort to make fixes. I am thinking of using DTS to run a procedure, then send out the error history report via email to myself and THEN roll back the procedure if there are any records in the error history report that way I know which statements have errors prior to the error history being wiped out w/ a rollback. Would this be a good approach? I am going to use DTS anyway to automate the running of the procedures anyway as these procedures check for new files on our system and process them automatically if they are new files.

    Also is there a way to capture the error text ie "Primary Key Violation......etc"?

    Thanks.

    ddave

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sorry - with SQL 2K you are a bit stuffed. There isn't really error handling as such in SQL 2k - testing for @@error is about your limit.

    There is no such thing as a truly nested transaction, so you will need to log your errors after the rollback. This also means that any success logging you perform during the transaction is lost.

    The number for @@ERROR corresponds to the entry in dbo.sysmessages (or dbo.messages - I forget for the SQL Server 2k system table name) so you can look up the error in there. It wont include the table and constraint names (for example) but if you are keeping track of your procedure logic then you can cross reference this.

    I would personally recommend to do no more development work in SQL 2k and seriously look at putting some time in to putting together an upgrade proposal. There have been three versions since SQL 2k and it is no longer on mainstream support.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    e.g.:
    Code:
    SELECT  1/0
    
    SELECT  *
    FROM    dbo.sysmessages
    WHERE   error       = @@ERROR
        AND msglangid   = 1033
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    ...and finally, Erland's articles are usually pretty close to definitive for their particular subject:
    Error Handling in SQL 2005 and later
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As Poots pointed out, Transact-SQL error handling in SQL 2000 is pretty limited. I've had decent success logging the basic information (error number, location, and date/time) about Transact-SQL errors using xp_logevent, but not so much luck getting the details from the string that is normally returned to the client.

    I've always had to resort to another way to log more complete error information, usually by scheduling the Transact-SQL code as a job and logging the output for analysis when a failure occurs.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Feb 2004
    Posts
    193
    Thanks again guys for getting back to me.

    I am pretty much stuck w/ 2000 on this one as it has been so a long time and I don't have any say in it being a lowly analyst.

    I got to sticking the @@Error value into a variable and then inserting it into a temporary table w/ a datestamp, error number and statement number. The statement number is something I created that identifies the exact procedure and statement of a procedure is having an issue. I simply numbered the statements in the procedures and created a table of contents so to speak at the top commented out of course. This has come in handy when I look for when a record was updated or inserted into the live data as well. My concern now is that I have to grab the list of errors if one exists before a rollback happens. I am working with trying to do these steps in DTS so I want to do a send mail task before I rollback that way I will have the list. I am not sure how this will work out. Thanks.

    ddave

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    To avoid the effects of the rollback and losing the data previously inserted into temporary tables for further processing, use table variables, that do not get affected by ROLLBACK TRAN.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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