Post reply

xp_readerrorlog

  • July 17, 2003 at 1:43 pm

    #59342

    Does anyone have a routine that uses xp_readerrorlog and concatinates the errorlog columns together for lines that have the continuationrow set to 1? I would like to get the complete error message into a single column or variable.

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • July 17, 2003 at 2:40 pm

    #465933

    CREATE TABLE #Errors (vchMessage varchar(8000), ID int)

    INSERT #Errors EXEC xp_readerrorlog

    SELECT vchMessage FROM #Errors where ID = 1

    Drop table #Errors

    Shas3

  • July 17, 2003 at 3:06 pm

    #465934

    Maybe I don't make myself total clear. What I want is to have is a complete error messages as a single column. So basically you need to concatinate the errorlog column from records that have continuationrow = 0 with the appropriate errorlog record that has continuationrow =1. So basically if you had the following three errorlog rows, the third row would be concatinated at the end of the second row to get a complete error message.

    Does this make sense.

    2003-07-17 09:29:20.17 spid52 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.. 0

    2003-07-17 09:33:59.03 backup Database backed up: Database: msdb, creation date(time): 2000/08/06(01:40:56), pages dumped: 3196, first LSN: 902:371:1, last LSN: 902:374:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\MSSQL\BA 0

    CKUP\this_is_a_test'}). 1

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • July 17, 2003 at 3:26 pm

    #465936

    In case you are interested I answered my own question. Here is my solution. Came to me as I when to get something out of the vending machines. Guess you need to get up and walk around to clear you head sometimes:

    -- table to holde error log

    create table #errorlog(id int identity, errorlog varchar(255), continuationrow int)

    -- build table that contains current errorlog

    insert into #errorlog exec xp_readerrorlog

    --delete from #errorlog where errorlog not like '%backup Database backed up: Database:%'

    select rtrim(a.errorlog) + COALESCE (b.errorlog,'') from (SELECT * FROM #errorlog WHERE continuationrow=0) A LEFT OUTER JOIN

    (SELECT * FROM #errorlog WHERE continuationrow=1) B ON A.ID = B.ID - 1

    drop table #errorlog

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • June 26, 2012 at 12:59 pm

    #1505793

    Awesome, Awesome! I have been trying to figure this out and here it is.

    Thank you so much for posting. 😀

    Cuppadan

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply