How to get sp_readerrorlog output into table and sending mail in html format.

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Implementation: Sending html format mail to recipients of ‘sp_readerrorlog’ in sql server.

Scenario:Lets say,Client wants to get the output of ‘sp_readerrorlog’ on mail with proper html and understandable format  and  you are asked to carry out this implementation.In this case,below queries will help you lot.

Infrastructure: Below queries and processes are compatible with SQL server2000,2005,2008 R2,2012,2014.


Steps:

  • First execute the ‘sp_readerrolog’ in ssms and see the number of columns along with its datatypes. Accordingly create a table in any user database.The new table should have same no of columns and data types as ‘sp_readerrorlog’.

sp_readerrorlog

  • Now create a table to capture the contents of ‘sp_readerrorlog’ in it.
Create table logs1
(
LogDate DATETIME,
ProcessInfo VARCHAR(255),
Text VARCHAR(MAX)
)
  • Now insert into logs1 from ‘sp_readerrorlog’ using below command.
INSERT INTO logs1
EXEC sp_readerrorlog
  • Now cross check once whether the table having contents of ‘sp_readerrolog’ by select table
SELECT * FROM logs1

Imp Note:Here,you can filter the table by putting where clause.

Generally we don’t want all the error log to be mailed.We want only some particular error related log to be mailed.Example: lets say you want login failure error,backup,log shipping error,deadlock error to be mailed.In this cases we can simple put where clause and filter out the text messages from table ‘logs’

Example:

SELECT * FROM logs1 where text like ‘login%’
GO
  • Create a stored procedure which will send the mail in html format to respective person.
  • Below is the stored procedure script-run it once and scheduled the job as per requirements.
Create Procedure [usp_sendErrorlogMail]
  as
  begin
declare @recipients varchar(max)  
declare @message varchar(5000)  
declare @query  varchar(max)  
declare @subject varchar(112)  
declare @attach_results varchar(50)  
set @recipients = ‘ ‘ ——–recipients mail ID.
set @message = ”  
set @query  = ”  
set @subject = ‘ sp_readerrorlog details’——subject for the mail   
set @attach_results = ‘True’  
IF Exists(select 1  from logs1)  —‘select 1 from logs’ is used to check whether logs1 table contents data or not.
begin
———- case 2 starts 
begin  
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
begin
SET @xml = CAST((SELECT [LogDate] AS ‘td’,”,[ProcessInfo] AS ‘td’,”,[Text] AS ‘td’ from 
(SELECT * FROM logs1 where TEXT like ‘login %’ ) tab  
FOR XML PATH(‘tr’), ELEMENTS ) AS NVARCHAR(MAX))
————————————————————–

SET @body =’

sp_readerrorlog details

 

This is system generated Report, Please do not reply.

 

 

 

LogDateProcessInfoText’

 

SET @body = @body + @xml +’

 ———- case 2 starts
begin  
  set @message = ‘Please Do not reply on this mail’  
  exec msdb..sp_send_dbmail  
@profile_name = ‘dba’ —–DB mail profile  
     , @recipients=@recipients  
     , @body = @body
     , @subject=  @subject  
     , @body_format =’HTML’
 end  
end  
end
end
else print(‘bye’)
end
  • Kindly download the above script from below link

https://app.box.com/s/byedt058tkbfp4r0pnmp2iankkz0xh4y

  •   Now execute the created SP’s to test.

exec usp_sendErrorlogMail

  • Now ask the recipient to check his/her mail.

mailoutput

  • Now you can simply create a new job in sql server.In first step put
    INSERT INTO logs1
    EXEC sp_readerrorlog

    and second step of job put the created Stored procedure name  i.e. ‘usp_sendErrorlogMail’

and in 3rd step truncate the logs1 table to control the unnecessary growth of the table.

truncate table logs1—————-truncate table
or simply merge the queries in stored procedure itself.
  • Done……

One thought on “How to get sp_readerrorlog output into table and sending mail in html format.

Comments are closed.