xp_readerrorlog

Question

Post reply

xp_readerrorlog

Greg Larsen

SSC-Insane

Points: 20961
More actions
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

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

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

Shas3 Hall of Fame Points: 3671 More actions · Answer 1

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

INSERT #Errors EXEC xp_readerrorlog

SELECT vchMessage FROM #Errors where ID = 1

Drop table #Errors

Shas3

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 2

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

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 3

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

Cuppadan SSC Enthusiast Points: 109 More actions · Answer 4

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

Thank you so much for posting. 😀

Cuppadan