Results 1 to 9 of 9

Thread: sysmessages

  1. #1
    Join Date
    Jan 2007
    Posts
    6

    Unanswered: sysmessages

    I would like to write a SQL stored procedure, to be used on a DBA dashboard website, showing errors that have happened on current system day. I have found that errors displayed in the SQL Server Logs are stored in sysmessages table but I cannot find a column showing date/time. Does anybody have any ideas where I can get the date/time of either when the error happened or when the error was written to the SQL Server Logs?

    Any suggestions much appreciated.


    Kind Regards

    Amber

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - just to make sure that neither of us are confused - you know that sysmessages simply contains all SQL Server's error messages and is not a log of errors that have occurred right?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Posts
    6
    Thank you for reply and I think you have spotted that sysmessages isn't want I am looking for, as after revisisted books online it seems to contain all the possible error messages that are available to be used by SQL Server. I would like my stored procedure to provide a list of errors that have occured as shown in SQL Server Logs under Management from Enterprise Manager.

    Please let me know if you need further information.

    Thank you for spotting my missunderstanding.


    Regards
    Amber

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    The SQL Server error logs are stored as text files in the operating system (typically in %install path%\LOGS). They have the names ERRORLOG, ERRORLOG.1, ERRORLOG.2, etc.

    Typcially, I think developers access these files using Scripting.Filesystem object. Alternatively, I thought there was a way to create a linked table to an textfile source, but I can't find a reference to it now. You couls also use SQL DMO (SQL 2000) or SQL SMO (SQL 2005) to read the error log.

    Regards,

    hmscott
    Have you hugged your backup today?

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    I have this snippet of code I found somewhere a few years ago. It might help:

    Code:
    /* SQL Log. Reading the SQL Log using Transact-SQL. 
    This query shows the entries from the latest SQL log (the one currently being updated).  You can modify this script according to what information you find important.  As you can see, I like to filter out various key works and entries that are part of  non-critical SQL Server messages. This script uses the undocumented extended stored procedure xp_readerrorlog:
    */
    PRINT 'SQL Log Review - last two days'
    CREATE TABLE #Errors (vchMessage varchar(255), ID int)
    CREATE INDEX idx_msg ON #Errors(ID, vchMessage)
    INSERT #Errors EXEC xp_readerrorlog
    SELECT vchMessage 
    FROM #Errors 
    WHERE  vchMessage NOT LIKE '%Log backed up%' 
      AND  vchMessage NOT LIKE '%.TRN%' 
      AND  vchMessage NOT LIKE '%Database backed up%' 
      AND  vchMessage NOT LIKE '%.BAK%' 
    --  AND  vchMessage NOT LIKE '%Run the RECONFIGURE%' 
      AND  vchMessage NOT LIKE '%Copyright (c)%'
      AND  (isdate(substring(vchMessage,1,10)) = 1) 
      AND  substring(vchMessage,1,10) > convert(varchar(10),(dateadd(dd,-2,getdate())),120) 
    ORDER BY ID 
    DROP TABLE #Errors

    -- This is all just a Figment of my Imagination --

  6. #6
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by tomh53
    xp_readerrorlog
    That's the one I remembered. I had forgotten that it is an xp_ and not an sp_.

    Thanks.

    hmscott
    Have you hugged your backup today?

  7. #7
    Join Date
    Jan 2007
    Posts
    6
    Thank you for the sp and code supplied. I think this will be great for the dashboard I am creating.

    Thank you for everyones contribution.


    Kind Regards
    Amber

  8. #8
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    If it goes commercial, i would like enough in royalties to buy a nice flat panel 20" screen. :-)

    -- This is all just a Figment of my Imagination --

  9. #9
    Join Date
    Jan 2007
    Posts
    6
    TomH, no worries if I become rich and famous as a result I won't forget the flat screen!


    Regards
    Amber

Posting Permissions

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