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, '<', '<' ), '>', '>' )
+ '</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, '<', '<' ), '>', '>' )
+ '</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,''), '<', '<' ), '>', '>' )
+ '</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
This is admirable it works
ReplyDelete
ReplyDeleteAs 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
take a look at vsql-email (sql-email.com) this tool is exactly what I needed
ReplyDeleteThank you for this valuable information. The information you shared is very interesting.
ReplyDeleteweb design company chennai | web development company chennai
ReplyDeletethank u for sharing such a wonderful topic for more info about oracle
oracle fusion procurement training
شركات تنظيف بمكة
ReplyDeleteشركة مكافحة حشرات
شركة تنظيف بالدمام
شركة تنظيف بالمدينة
شركة تنظيف بالطائف
https://www.behance.net/gallery/46472895/_
ReplyDeletehttps://www.behance.net/gallery/46463613/_
https://www.behance.net/gallery/46463247/_
https://www.behance.net/gallery/46451097/_
https://www.behance.net/gallery/46460639/_
https://www.behance.net/gallery/46462575/_
https://www.behance.net/gallery/46450923/_
ReplyDeletehttps://www.behance.net/gallery/46450419/_
https://www.behance.net/gallery/46430977/-jumperadscom
https://www.behance.net/gallery/42972037/_
https://www.behance.net/gallery/40396873/Transfer-and-relocation-and-Furniture-in-Dammam
https://www.behance.net/gallery/51576047/_
This blog had an extremely strong impact on me.
ReplyDeletehttps://www.duocircle.com/email/phishing-protection/
xender download
ReplyDeletexender apps
xender web
xender software
In application development, we are required to build a SQL program to perform certain task(s) on periodic basis.
ولا ننسى تنظيف المنزل بعد اعمال التشطيب والدهانات من خلال ارخص شركة تنظيف بمكه ولأننا نعتبر انفسنا وشركتنا افضل شركة نظافة بمكة فلا بد من التواصل معنا بخصوص تنظيف الأرضيات بعد الدهانات وعمل تنظيف شامل للشقة او المنزل من خلال شركه تنظيف بمكه المكرمه لعمل ما يلزم من تنظيف وتلميع للمنزل قبل نقل العفش اليه فانت لو تحتاج الى شركه نقل عفش بمكه المكرمه فانت في المكان الصحيح ومهما بحثت فلن تجد شركات نقل عفش بمكة افضل من شركة آدم لأن شركتنا تعتبر افضل شركه نقل عفش بمكة وفي نفس الوقت تعتبر ارخص شركه نقل عفش بمكه وقد استطعنا ان نجمع بين الأفضلية وقلة الأسعار بجهد متواصل من ادارة شركتنا
ReplyDeleteThank you for sharing your info. tutuapp
ReplyDeleteredboxtv
ReplyDeleteredbox tv
redboxtv app
redboxtv download
we are required to build a SQL program to perform certain task(s) on periodic basis.