Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Unanswered: Capturing WARNINGS to write to error log?

    Hey y'all...

    Anyone know how to capture SQL Warnings so I can write them to an error log? I can't seem to find any info on it in Books Online...

    I can capture the errors just fine by using @@ERROR after a select, but what about warnings such as
    Code:
    "Warning: Null value is eliminated by an aggregate or other SET operation."
    Thanks!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = 'NULL_VALUE'))
    EXECUTE msdb.dbo.sp_delete_alert @name = N'NULL_VALUE'
    EXECUTE msdb.dbo.sp_add_alert @name = N'NULL_VALUE', @message_id = 8153, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 4, @category_name = N'[Uncategorized]'
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Warnings get returned as messages in the TDS stream, much like PRINT statements do. I don't think that they generate an internal event or change any spid state information, so I don't think that there is any way for a script/stored procedure to capture them.

    I'm pretty sure that you can modify the client to capture warning messages and the text (after substitution) of error messages, but I can't think of any way to capture it on the server side.

    -PatP

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Pat, do me a favor and replicate on your TEST system the following screens. I saved the actual error message screens, but before posting this also experimented with warnings that you called "like PRINT statements." So that there won't be any further confusion, anything that is stored in sysmessages can be logged to the errorlog of both SQL Server and Windows Application log, as long as the check box in screen #3 is checked.
    Attached Thumbnails Attached Thumbnails Alert-1.bmp   Alert-2.bmp   Alert-3.bmp  
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Will do, but it will be a couple of days before I have the opportunity. I'm traveling now, and when I get back to the orifice I'll be working on other issues for a couple of days. It will probably be 2004-07-29 before I really have a chance to try it.

    -PatP

Posting Permissions

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