Search SQL Error Log

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Alejandro Pelc
-- Create date: 03/05/2009
-- Description: Procedure to search in Error Log
-- =============================================
CREATE PROCEDURE sp_SearchSQLErrorLog
@AllLogs bit,
@LogFile int = null,
@SearchString1 varchar(255) = null,
@SearchString2 varchar(255) = null
AS
BEGIN
SET NOCOUNT ON
if((@AllLogs = 0) and (@LogFile is null))
begin
RAISERROR (N'You must specify a valid log file.',11, 1)
end
else
begin
declare @tblerrors table(
logfile tinyint null,
logdate datetime,
process varchar(200),
descript nvarchar(max)
)
declare @msg varchar (100)
declare @NumErrorLogs int
declare @loop tinyint
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', @NumErrorLogs OUTPUT
if(@AllLogs = 1)
begin
set @loop = 0
while(@loop < @NumErrorLogs)
begin
insert into @tblerrors (logdate,process,descript)
exec('sp_readerrorlog '+ @loop + ',1,''' + @SearchString1 + ''', ''' + @SearchString2 + '''')
update @tblerrors set logfile = @loop + 1 where logfile is null
set @loop = @loop + 1
end
if exists(select 1 from @tblerrors)
select logfile as 'Log File N°',logdate as 'Date',process as 'Process',descript as 'Message' from @tblerrors order by logdate
else
print 'No records found that matches your search criteria.'
end
else
begin
if(@LogFile<>0)
begin
set @LogFile = @LogFile - 1
if(@LogFile < @NumErrorLogs)
begin
insert into @tblerrors (logdate,process,descript)
exec('sp_readerrorlog '+ @LogFile + ',1,''' + @SearchString1 + ''', ''' + @SearchString2 + '''')
update @tblerrors set logfile = @LogFile + 1 where logfile is null
if exists(select 1 from @tblerrors)
select logfile as 'Log File N°',logdate as 'Date',process as 'Process',descript as 'Message' from @tblerrors order by logdate
else
print 'No records found that matches your search criteria.'
end
else
begin
set @msg = 'You must specify a valid log file. The logfile ' + cast(@LogFile as nvarchar) + ' is invalid. Valid values are 1 to ' + cast(@NumErrorLogs as nvarchar)
RAISERROR (@msg,11, 3)
end
end
else
begin
set @msg = 'You must specify a valid log file. The logfile ' + cast(@LogFile as nvarchar) + ' is invalid. Valid values are 1 to ' + cast(@NumErrorLogs as nvarchar)
RAISERROR (@msg,11, 2)
end
end
end
END