This post is related to this tip: Reading the SQL Server log files using T-SQL

http://www.mssqltips.com/tip.asp?tip=1476

I was not aware of this functionality but I cannot seem to get the search facility to work in SQLServer 2000. It works fine in SQLServer 2005 but when I run EXEC sp_readerrorlog 1, null, 'master' (EXEC sp_readerrorlog 1, null, 'master'  actually returns an error in SQLServer 2000) 

in SQLServer 2000 it does not narrow the result set and just returns everything. Something similar happens with xp_readerrorlog.

Any ideas ?

Many thanks,

Peter A.

I think the format for SQL Server 2000 is different than SQL Server 2005.

Take a look at this article:

http://vyaskn.tripod.com/sp_readerrorlog_undocumented.htm

Regards,
Greg

Many thanks Greg.

This procedure takes 7 parameters:
1.        Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
2.        Log file type: 1 or NULL = error log, 2 = SQL Agent log
3.        Search string 1: String one you want to search for
4.        Search string 2: String two you want to search for to further refine the results
5.        Search from  start time
6.        Search to end time
7.        Sort order for results: N'asc' = ascending, N'desc' = descending

--the 5 and 6 paramenters use VARCHAR type，desc
declare @Time_Start varchar(30);
declare @Time_End varchar(30);
set @Time_Start=convert(varchar(30),getdate()-5,25);
set @Time_End=convert(varchar(30),getdate(),25);
EXEC master.dbo.xp_readerrorlog 0, 1, 'Failed', 'login', @Time_Start, @Time_End, N'desc';

--the 5 and 6 paramenters use DATETIME type
declare @Time_Start datetime;
declare @Time_End datetime;
set @time_start=getdate()-5;
set @Time_End=getdate();
EXEC master.dbo.xp_readerrorlog 0, 1, 'Failed', 'login', @Time_Start, @Time_End, N'desc';

Great tip,

Parameters 6 and 7 are between dates!!!

exec xp_readerrorlog 0, 1,'succeeded','pardo','2008-06-23 10:06:59.250','2008-06-24 16:40:56.790','asc'

It is only for  SQL Server 2005

 Pardo