check error log via sql only for severity 17+

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

check error log via sql only for severity 17+

Author

Topic

snomad
Starting Member

22 Posts

Posted - 2009-08-25 : 09:38:01

Hi all,

I may be 'aving a mo but I can't suss this today.

SQL 2005. I want to automate my error log checking to pull out only errors with a severity of 17 +. The trouble is that the output of xp_readerrorlog spreads an error over 2 rows.
1). Error: 14421, Severity: 16, State: 1.
2). The log shipping secondary database ..... is out of sync.
So whilst i need to search for severity 16, 17 - 25, then select that row PLUS the next one.

I have this so far:
create temp table
xp_readerrorlog 0,1 into temp table
alter temp table to add a kind of id /pk type field
then what, how do number the rows?
or is there a better way?

pls help!

Thank you.

Thank you!!

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-25 : 09:50:48

You can create the temp table with a row number that's an identity (1,1)

An infinite universe is the ultimate cartesian product.

snomad
Starting Member

22 Posts

Posted - 2009-08-25 : 11:14:55

Hello again! Thanks for the reply. The trouble is if i create temp table with an extra identity col then i can't exec xp_readerrorlog into it (has to have same no. of cols as output i think). And if i alter the temp table to add a column, i can't create as identity. Do you see or am i being a spanner?

Thank you!!

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-25 : 11:38:37

As cat_jesus says, create a temp table with the identity col. Then
INSERT INTO temp_table (all other columns listed here)
EXEC xp_readerrorlog 0,1

Now you'll have all the data rows numbered.

Also, you can use alerts to notify you of any high severity errors.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-25 : 11:42:59

You can't? Why not?

create table #temp (
rownum INT identity(1,1)
, LogDate datetime
, ProcessInfo varchar(20)
, log_text varchar(7000)
, log_date as convert(datetime, convert(varchar(20), LogDate, 106))
)
insert #temp (LogDate, ProcessInfo, log_text) exec xp_readerrorlog 1, 1, 'ERROR'

An infinite universe is the ultimate cartesian product.

snomad
Starting Member

22 Posts

Posted - 2009-08-25 : 12:02:58

oh my goodness, you're both right and i'm being a spanner!! thank you for the replies!! that's perfect. :)

Thank you!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources