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.
- 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.
- Now insert into logs1 from ‘sp_readerrorlog’ using below command.
- Now cross check once whether the table having contents of ‘sp_readerrolog’ by select table
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’
- 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.
SET @body =’
This is system generated Report, Please do not reply.
SET @body = @body + @xml +’‘
- Kindly download the above script from below link
- Now execute the created SP’s to test.
- 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 logs1EXEC 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.