Tuesday, August 6, 2013

Return SQLResult set into a HTML format.(Microsoft Sql server)

Please find the following script, which will show the backup status of db's in html format.


                                           
-- 
























=============================================
-- HariPrasad Ere 08/06/2013
-- Send BackUpStatus For each db
-- =============================================

--Create a Temp table to insert last backcompleted records.
USE msdb 
CREATE TABLE #Backuptbl
(
backup_finish_date DATETIME,
database_name      VARCHAR(50)
)
--Insert the previous day backup completed records into #Backuptable 

INSERT INTO #Backuptbl (backup_finish_date,database_name )
SELECT MAX(backup_finish_date) AS BackUp_Finish_Date ,database_name
FROM backupset b inner join
sys.databases d
ON b.database_name = d.name 
WHERE backup_finish_date < GETDATE() 
GROUP BY database_name 
ORDER BY database_name DESC

--Create Email Message

DECLARE @Subject VARCHAR(100)
SET @Subject = ' DataBase BackUp Status' + CONVERT(VARCHAR(10),GETDATE()-1,101)
Declare @Message VARCHAR(MAX)
SET @Message = 'Team, <BR> <BR>' 
SET @Message = @Message + CHAR(13) + CHAR(10) + 'LAST BackUp Finish Date : ' 

SET @Message = @Message + CHAR(13) + CHAR(10) + '<Table border = 1 width = 50%>'
SET @Message = @Message + CHAR(13) + CHAR(10) + 
'<tr><td width  = 25%><font face = Arial align = left ><B>DataBaseName</B></font></td><td width  = 25%><font face = Arial align = left><B>backup_finish_date</B></font></td></tr>'

Select @Message = @Message + CHAR(13) + CHAR(10) +'<td><font face = Arial>' + database_name

+ '</font></td>' +'<td nowrap><font face = Arial>' +  CONVERT(VARCHAR(10),backup_finish_date, 101)+ '</font></td></tr>'

FROM #Backuptbl
--copy the fllowing text message and paste into a notepad, Save the notepad as HTMl. open the saved html to view the results. you can call variable@message from sp_sendmail master db stored procedure.
SELECT @message
--clean up the objects
DROP  TABLE #Backuptbl

No comments:

Post a Comment