SQL SERVER DBA, Linux and Azure: Sending query results to HTML Format through e-mail in SQL Server

Sending query results to HTML Format through e-mail in SQL Server

--html--

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)


SET @xml = CAST(( select User ID AS 'td','', Name AS 'td','',
       SECurity AS 'td','', LST_LOGIN AS 'td','',LOGIN_FAIL AS 'td','',
       LOGIN_inactive AS 'td'
FROM  DBA..table_Nmae
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
--select @xml


SET @body ='<html><body><H3>DBA_audit permissions Monthly Report</H3>
<table border = 1>
<tr>
<th> User ID </th> <th> Name </th> <th> SECurity </th> <th> LST_LOGIN </th> <th> LOGIN_FAIL </th>
<th> LOGIN_inactive </th>  </tr>' 


SET @body = @body + @xml +'</table></body></html>'


EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ajayMail',
@body = @body,
@body_format ='HTML',
@recipients = 'ajeyudub@sqlajay.com',
@subject = 'DBA_audit permissions Monthly Report' ;

No comments:

Post a Comment