Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    268

    Unanswered: Question about SQL Server errors/exceptions

    In SQL Profiler, I see Exception records with a TextData entry like:
    Error: 2601, Severity: 14, State: 3

    There is no other error text for that entry and there are no corresponding log entries. When I look up the help for error 2601, I see:

    Message Text:
    Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'.

    That's obviously a C sprintf format string. I would expect to see the resulting formatted text somewhere. Where would I find detail on these errors?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The only place that I know to harvest these messages is in the TDS stream bound for the client. I don't think there is any way to trap them via stored procedures, logging (SQL or NT), or other means. If you find any answers (satisfactory or not), I'd like to hear about them!

    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You're probably retrieving only the first element of the error collection. You need to scroll through it either with FOR or with WHILE/DO...LOOP/etc. to retrieve all messages associated with the error.


    Logging on the other hand can be easily enabled for this particular message by executing the following:

    exec sp_altermessage 2601, with_log, true
    go
    exec msdb..sp_add_alert @name = N'test_alert', @message_id = 2601, @severity = 0, @enabled = 1, @delay_between_responses = 0, @include_event_description_in = 0, @category_name = N'[DEFAULT]', @raise_snmp_trap = 0
    go
    exec msdb..sp_update_alert @name = N'test_alert', @job_id = 0x00000000000000000000000000000000 , @delay_between_responses = 60, @include_event_description_in = 5
    go
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    At least the way I read the original question was to see if the script/sproc could retrieve the text of the error message after substitution. I don't know how that can be done, since the text is passed into the TDS stream without signaling an event within the SQL engine (or setting an @@variable).

    Using sp_altermessage would be a good first step, since it could trap the error message in the log file, but I still can't devine a way to get that back to the procedure that caused the error.

    -PatP

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Pat Phelan
    ...the text is passed into the TDS stream without signaling an event within the SQL engine (or setting an @@variable)...
    -PatP
    The @@error global memory variable contains the last error that occurred, whether it's logged or not. I just ran a test on 2601 and the following statement returned what I expected:

    --Attempt to insert a duplicate value while UNIQUE indes exists on the field...
    set @ErrorNumberFromGlobalErrorVariable = @@error
    select * from master.dbo.sysmessages where error = @ErrorNumberFromGlobalErrorVariable
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How does this get the values for either instance of %s ? Am I missing something ?

    -PatP

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I think you do, because by the time "set @ErrorNumberFromGlobalErrorVariable=@@error" completes, the error is already logged to the errorlog with fully expanded string substitution for every instance of the place holder. The client is about to be presented with the error in the same format.
    "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
  •