I' m able to get this done. But I don't think this is correct way creating tables and dropping tables and selecting data twice.Can you please provide me a better script that monitors and sends an email when it finds an error in error log in last hour?

Script that worked:

CREATE TABLE #ErrorLog (

LogDate DATETIME,

ProcessInfo NVARCHAR(255),

LogText NVARCHAR(MAX)

)

INSERT INTO #ErrorLog (

[LogDate],

[ProcessInfo],

[LogText]

)

EXEC xp_readerrorlog

select left(@@servername,20)as servername, left(LogDate,20) as logdate,[LogText] into Errorlog from #ErrorLog

where ProcessInfo !='Logon'

and LogText not like 'Database backed up%'

and LogText not like 'Log was backed up%'

and LogText not like '%found 0 errors%'

and LogText not like 'This instance of SQL Server has been using a process ID%'

and LogText not like 'Configuration option ''user options'' changed from 0 to 0. Run the RECONFIGURE statement to install.'

and LogText not like 'Microsoft SQL Server 200%'

and LogText not like '(c) %'

and LogText not like 'All rights rese%'

and LogText not like 'Server process ID is%'

and LogText not like 'System Manufacturer:%'

and LogText not like 'Authentication mode is %'

and LogText not like 'Logging SQL Server messages in file%'

and LogText not like 'This instance of SQL Server last reported using a process ID o%'

and LogText not like 'Registry startup parameters:%'

and LogText not like 'SQL Server is starting at normal priority base%'

and LogText not like 'Detected % CPUs. This is an informational messag%'

and LogText not like 'Using locked pages for buffer pool%'

and LogText not like 'Using dynamic lock allocation.%'

and LogText not like 'Node configuration: node 0: CPU mask%'

and LogText not like 'Setting database option COMPATIBILITY_LEVEL to 100%'

and LogText not like 'Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.'

and LogText not like 'Server is listening on %'

and LogText not like 'Server local connection provider is ready to accept connection on%'

and LogText not like 'The SQL Server Network Interface library successfully registered the Service Principal Name%'

and LogText not like 'Service Broker manager has started.'

and LogText not like 'Starting up database%'

and LogText not like 'CHECKDB for database % finished without errors on %'

and LogText not like 'FILESTREAM: effective level = 0, configured level = 0%'

and LogText not like 'AppDomain % unloaded.'

--change the 72 below to alter timeframe of log read.

and LogDate> DATEADD(hh, - 1, GETDATE())

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'dbmail',

@recipients = 'abc@abc.com',

@query = 'select * from Errorlog',

@subject='error log error'

drop table #ErrorLog

drop table Errorlog