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’.

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%’

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]

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

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.

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.