Create HTML from TSQL / SQL Server Queries

  1. Below code can be used to create HTML output as a file from TSQL / SQL Server queries,
/*
Script         : TSQL_Using_HtmlFile.sql
Version        : 1.0
Date           : 14/03/2021
Tested         : SQL server 2008 and above
Requirement    : User running the script should be a SYSADMIN.
		 sp_configure 'xp_cmdshell' and 'Ole Automation Procedures' should be enabled.
Errors or BUGS : Please send an email to Vivek Janakiraman (jvivek2k1@yahoo.com)
Author         : Vivek Janakiraman
*/
use tempdb
go
Set NoCount On;
SET concat_null_yields_null OFF;

Declare @Body nvarchar(max)
,  @TableHead varchar(max)
,  @TableTail varchar(max)

Declare @file nvarchar(max)
Declare @filelocation nvarchar(max)
Declare @filename nvarchar(max)
Declare @Final_filename nvarchar(max)
Declare @Source nvarchar(max)
DECLARE @OLE INT
DECLARE @FileID INT
DECLARE @file_temp varchar(max)
exec xp_cmdshell 'md c:\temp'
set @filelocation = 'c:\temp\'
set @Final_filename = @filelocation + 'counter.txt'


Set @TableTail = '</table></body></html>';
Set @TableHead = '<html><head>' +
'<style>' +
'td {align=center;padding-left:5px;padding-right:5px;padding-top:3px;padding-bottom:3px;font-size:11pt;} ' +
'</style>' +
'</head>' +
'<body><table cellpadding=0 cellspacing=0 border=0 align=center WIDTH=1700>' +
'<tr bgcolor=C6CFFF><td align=center><b>SQL REPORT</b></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center><u><b>Object Details:</b></u></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center></td>' +
'</tr></table>';

Select @Body = '<table style=\"font-family: Calibri; font-size:85%; \" ALIGN=center BORDER=1 CELLPADDING=1 CELLSPACING=1 WIDTH=1700 WIDTH=1700>
<tr bgcolor=FFEFD8>
      <td><b>ObjectName</b></td>
      <td><b>type_desc</b></td>
</tr>'

Select @Body = @Body + (select top 10
name As [TD]
,type_desc As [TD] 
from sys.objects 
For XML raw('tr'), Elements)

Set @Body = Replace(@Body, '_x0020_', space(1))
Set @Body = Replace(@Body, '_x003D_', '=')
Set @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=C6CFFF>')
Set @Body = Replace(@Body, '<TRRow>0</TRRow>', '')
Select @Body = @TableHead + @Body + @TableTail
set @Final_filename = @filelocation + 'Vivek.html'
EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT
EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, @Final_filename, 8, 1
EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, @Body
EXECUTE sp_OADestroy @FileID
EXECUTE sp_OADestroy @OLE

Below file can be viewed from location C:\temp.

2. Below code can be used to create HTML output as an email from TSQL / SQL Server queries,

/*
Script         : TSQL_Using_Html_Email.sql
Version        : 1.0
Date           : 14/03/2021
Tested         : SQL server 2008 and above
Requirement    : User running the script should be a SYSADMIN.				 
Errors or BUGS : Please send an email to Vivek Janakiraman (jvivek2k1@yahoo.com)
Author         : Vivek Janakiraman
*/
use tempdb
go
Set NoCount On;
SET concat_null_yields_null OFF;

Declare @Body nvarchar(max)
,  @TableHead varchar(max)
,  @TableTail varchar(max)

Set @TableTail = '</table></body></html>';
Set @TableHead = '<html><head>' +
'<style>' +
'td {align=center;padding-left:5px;padding-right:5px;padding-top:3px;padding-bottom:3px;font-size:11pt;} ' +
'</style>' +
'</head>' +
'<body><table cellpadding=0 cellspacing=0 border=0 align=center WIDTH=1700>' +
'<tr bgcolor=C6CFFF><td align=center><b>SQL REPORT</b></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center><u><b>Object Details:</b></u></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center></td>' +
'<tr><td align=center></td>' +
'</tr></table>';

Select @Body = '<table style=\"font-family: Calibri; font-size:85%; \" ALIGN=center BORDER=1 CELLPADDING=1 CELLSPACING=1 WIDTH=1700 WIDTH=1700>
<tr bgcolor=FFEFD8>
      <td><b>ObjectName</b></td>
      <td><b>type_desc</b></td>
</tr>'

Select @Body = @Body + (select top 10
name As [TD]
,type_desc As [TD] 
from sys.objects 
For XML raw('tr'), Elements)

Set @Body = Replace(@Body, '_x0020_', space(1))
Set @Body = Replace(@Body, '_x003D_', '=')
Set @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=C6CFFF>')
Set @Body = Replace(@Body, '<TRRow>0</TRRow>', '')
Select @Body = @TableHead + @Body + @TableTail

EXEC msdb.dbo.sp_send_dbmail @body = @Body
,@body_format = 'HTML'
,@profile_name = N'DBMAIL'
,@recipients = N'jvivek2k1@yahoo.com'
,@Subject = N'Object Details'

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Availability Group – SQL Server Agent Job to run only on Always On Primary Replica

-> I had a requirement to execute jobs only in primary replica.

-> To all jobs that should only be executed on Always On primary replica. I added 1 step as the first step of the job and another step as last step of that job.

-> 1st step of these jobs should execute below TSQL Query,

if (select
ars.role_desc
from sys.dm_hadr_availability_replica_states ars
inner join sys.availability_groups ag
on ars.group_id = ag.group_id
where ag.name = '<AGNAME>'
and ars.is_local = 1) = 'PRIMARY'
begin
Print 'This is Primary Replica'
end
else
begin
RAISERROR('This job should not run in Secondary',16,1)
end
-> For 1st step,

On success action : Go to the next step
On failure action: Go to step: [n] Last_Step ( It should basically go to the last step)

-> Last step of the job should execute the below TSQL,

print 'Job_Completion'
-> For Last step,

On success action : Quit the job reporting Success
On failure action: Quit the job reporting failure

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Viewing Allocation Unit Size of a NTFS Disk

-> Open an “Administrator – Windows Powershell ISE”.

-> Execute below command,

fsutil fsinfo ntfsinfo C:

-> Output below,

Fsutil_Alloc_Size.PNG

-> “Bytes per Cluster” provides us the Allocation unit size. In my case it is 4 KB.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights