Pages

Thursday 4 October 2012

SQL Server: Sending Email in HTML Table Format Using TSQL

As part of our DBA life , we have to configure many alert or statistical  mails which gives an idea about the state of the database server. Let us discuss how we can send an email in HTML table format using TSQL. I am just going to give a sample script and it is self explanatory.

USE MSDB
GO
DECLARE @Reportdate DATE
SET @Reportdate =CONVERT(VARCHAR(10),GETDATE(),121)

/**************************************************************

           full backup Header
***************************************************************/

DECLARE @FullBackupHeader VARCHAR(MAX)

SET @FullBackupHeader='<font color=black bold=true size= 5>'
SET @FullBackupHeader=@FullBackupHeader+'<BR /> Full Backup Report<BR />' 
SET @FullBackupHeader=@FullBackupHeader+'</font>'
/**************************************************************
           full backup report Section
***************************************************************/
DECLARE @FullBackupTable VARCHAR(MAX)    
SET @FullBackupTable= CAST( (    
SELECT td = name + '</td><td>' + BackupType + '</td><td>'+  FileName + '</td><td>'  
Startdate + '</td><td>'  + FinishDate+ '</td><td>' + Duration + '</td><td>'  +BackupSize
'</td><td>'  +CompressionRatio 
FROM (    
     
      
SELECT 

       sd.name,
       ISNULL(db.[Backup Type],'0') AS [BackupType]
       ISNULL(DB.Physical_device_name,'No Backup') AS 'FileName',
       CAST(ISNULL(DB.backup_start_date,'1900-01-01') AS VARCHAR(24))  AS Startdate ,
       CAST(ISNULL(DB.backup_finish_date,'1900-01-01') AS VARCHAR(24)) AS FinishDate,
       CAST(ISNULL(DB.Duration,'0') AS VARCHAR(24)) AS Duration,
       LEFT(CAST(ISNULL(Backupsize,0)AS VARCHAR(100)),4)+' GB' AS BackupSize,
       LEFT(CAST(ISNULL(ratio,0)AS VARCHAR(100)),5)+'%' AS CompressionRatio FROM 
       SYS.SYSDATABASES sd LEFT JOIN 
       (
       SELECT 
       bm.media_Set_id,
       'FullBackup' AS 'Backup Type'
       bm.Physical_device_name ,
       backup_start_date,
       backup_finish_date,
       Duration
CONVERT(VARCHAR(5),DATEDIFF(second,backup_start_date,backup_finish_date)/60) + ':' +  
RIGHT('00' + CONVERT(VARCHAR(5),DATEDIFF(second,backup_start_date,backup_finish_date)%60),2),
       database_name,
       ROUND((compressed_backup_size)/1024/1024/1024,2) AS Backupsize ,
       100-(compressed_backup_size*100/backup_size) AS ratio
       FROM msdb..backupmediafamily BM 
       INNER JOIN msdb..backupset bs ON bm.media_Set_id = bs.media_Set_id 
       WHERE [type]='D' AND backup_start_date>=DATEADD(dd,-1,@Reportdate) AND 
backup_start_date<=@Reportdate
       ) db ON sd.name=db.database_name
      ) AS d ORDER BY BackupType
  FOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) )    

  
  
SET @FullBackupTable= '<table cellpadding="0" cellspacing="0" border="1">'    

              + '<tr><th width="10">Database Name</th>
              <th  width="20">Backup Type</th>
              <th width="80">File Name</th>
              <th width="100">Start Date</th>
              <th width="40">Finish Date</th>
              <th width="40">Duration</th>
              <th width="10">Backup Size</th>
              <th width="40">Compression Ratio</th></tr>'    
              + REPLACE( REPLACE( @FullBackupTable, '&lt;', '<' ), '&gt;', '>' )   
              + '</table>' 
             /**************************************************************
           differential backup Header
***************************************************************/
DECLARE @DiffBackupHeader VARCHAR(MAX)
SET @DiffBackupHeader ='<font color=black bold=true size= 5>'
SET @DiffBackupHeader =@DiffBackupHeader +'<BR /> Differential Backup Report<BR />' 
SET @DiffBackupHeader =@DiffBackupHeader +'</font>'
/**************************************************************
           Differential backup Section
***************************************************************/
DECLARE @DiffBackupTable VARCHAR(MAX)    
SET @DiffBackupTable= CAST( (    
SELECT td = name + '</td><td>' + BackupType + '</td><td>'+  FileName + '</td><td>'  
Startdate + '</td><td>'  + FinishDate+ '</td><td>' + Duration + '</td><td>'  +BackupSize
'</td><td>'  +CompressionRatio 
FROM (    
       SELECT 
       sd.name,
       ISNULL(db.[Backup Type],'0') AS [BackupType]
       ISNULL(DB.Physical_device_name,'NO BACKUP') AS 'FileName'  ,
       CAST(ISNULL(DBB.backup_start_date,'1900-01-01') AS VARCHAR(24))AS Startdate ,
       CAST(ISNULL(DB.backup_finish_date,'1900-01-01') AS VARCHAR(24)) AS FinishDate,
       CAST(ISNULL(DB.Duration,'0') AS VARCHAR(24)) AS Duration,
       LEFT(CAST(ISNULL(Backupsize,0) AS VARCHAR(100)),6)+' MB' AS BackupSize,
       LEFT(CAST(ISNULL(ratio,0)AS VARCHAR(100)),5)+'%' AS CompressionRatio  
       FROM SYS.SYSDATABASES sd LEFT JOIN 
       (
           SELECT 
           bm.media_Set_id,
           'Differential Backup' AS 'Backup Type',
           bm.Physical_device_name ,
           backup_start_date,
           backup_finish_date,
           Duration
CONVERT(VARCHAR(5),DATEDIFF(second,backup_start_date,backup_finish_date)/60) + ':' +  
RIGHT('00' + CONVERT(VARCHAR(5),DATEDIFF(second,backup_start_date,backup_finish_date)%60),2),
           database_name,
           ROUND((compressed_backup_size)/1024/1024,2) AS Backupsize ,
           100-(compressed_backup_size*100/backup_size) AS ratio
           FROM msdb..backupmediafamily BM INNER JOIN msdb..backupset bs ON bm.media_Set_id
bs.media_Set_id 
           WHERE TYPE='I'  AND backup_start_date>=DATEADD(dd,-1,@Reportdate) AND 
backup_start_date<=@Reportdate
       ) db ON sd.name=db.database_name
       ) AS d ORDER BY BackupType
      FOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) )    
 
    
SET @DiffBackupTable= '<table cellpadding="0" cellspacing="0" border="1">'    

              + '<tr><th width="10">Database Name</th>
              <th  width="20">Backup Type</th>
              <th width="80">File Name</th>
              <th width="100">Start Date</th>
              <th width="40">Finish Date</th>
              <th width="40">Duration</th>
              <th width="10">Backup Size</th>
              <th width="40">Compression Ratio</th></tr>'    
              + REPLACE( REPLACE( @DiffBackupTable, '&lt;', '<' ), '&gt;', '>' )   
              + '</table>' 

/**************************************************************

   Empty Section for giving space between table and headings
***************************************************************/
DECLARE @emptybody2 VARCHAR(MAX)  
SET @emptybody2=''  
SET @emptybody2 = '<table cellpadding="5" cellspacing="5" border="0">'    
              
              '<tr>
              <th width="500">               </th>
              </tr>'    
              + REPLACE( REPLACE( ISNULL(@emptybody2,''), '&lt;', '<' ), '&gt;', '>' )   
              + '</table>'    
/**************************************************************
           Sending Email
***************************************************************/
              DECLARE @subject AS VARCHAR(500)    
DECLARE @importance AS VARCHAR(6)    
DECLARE @EmailBody VARCHAR(MAX)
SET @importance ='High'     
DECLARE @recipientsList VARCHAR(8000)
SELECT @recipientsList = 'Dba@PracticalSqlDba.com;nelsonaloor@PracticalSqlDba.com'

SET @subject = 'Backup Report of MYSql Instance'     

SELECT @EmailBody 
=@FullBackupHeader+@emptybody2+@FullBackupTable+@emptybody2+@DiffBackupHeader 
+@emptybody2+@DiffBackupTable
EXEC msdb.dbo.sp_send_dbmail    
@profile_name ='MyMailProfile',    
@recipients=@recipientsList,
@subject = @subject ,    
@body = @EmailBody ,    
@body_format = 'HTML' ,    
@importance=@importance    


You can download the formatted script from here. I have implemented this method to send various statistical/alert mail. Hope this will help you.

If you liked this post, do like my page on FaceBook

14 comments:


  1. As we know that SQL language is specified as an ANSI and ISO standard and performance, scalability, and optimization are important for database-driven applications, especially on the Web. In application development, we are required to build a SQL program to perform certain task(s) on periodic basis or daily basis. For example, send email alert of order details from SQL Server to the business users. Let us discuss how we can send an email in HTML table format using TSQL.

    Sending Email in HTML Table Format Using TSQL in SQL Server

    ReplyDelete
  2. take a look at vsql-email (sql-email.com) this tool is exactly what I needed

    ReplyDelete
  3. Thank you for this valuable information. The information you shared is very interesting.
    web design company chennai | web development company chennai

    ReplyDelete

  4. thank u for sharing such a wonderful topic for more info about oracle
    oracle fusion procurement training

    ReplyDelete
  5. xender download
    xender apps
    xender web
    xender software
    In application development, we are required to build a SQL program to perform certain task(s) on periodic basis.

    ReplyDelete
  6. Thank you for sharing your info. tutuapp

    ReplyDelete
  7. redboxtv
    redbox tv
    redboxtv app
    redboxtv download

    we are required to build a SQL program to perform certain task(s) on periodic basis.

    ReplyDelete
  8. It's always good to hear, "buy one and get one free". But with Seers Cookie Consent you can get many more. I will gladly suggest Seers for its tools to generate a complaint Cookie Policy. Do check out its Cookie Consent Banner; it indicates the level of concern Seers possess for its customer's data.
    PECR Cookie Requirement

    GDPR E training


    data protection impact assessment

    gdpr cookie notice

    cookie consent popup
    ccpa compliance

    ReplyDelete
  9. The Institute of Data Protection (IDP) is one of the most forward thinking and advanced learning programmes available for data protection professionals. We represent and support our members, promoting the highest professional standards around data protection and privacy issues.
    Cookie policy

    ReplyDelete
  10. Yowhatsapp 2020 is getting recognition day by day. People are very dependent on different apps like Facebook, Instagram, WhatsApp, etc. all these apps use for entertaining and communication.

    AntiBan YoWhatsApp 2020

    ReplyDelete
  11. GOGO Live 2020 is a live streaming app that allows its members to communicate with the people all around the world.

    GOGO LIVE MOD APK

    ReplyDelete
  12. Best Attitude Whatsapp Status, Best Attitude Quotes for Whatsapp & Facebook, Attitude style status, Attitude Status for boys and girls.

    Hindi Attitude Status

    ReplyDelete