February 23, 2017 at 8:10 am
Hello,
I have a scheduled task - running a batch file, which copies files from a shared server, configures the data and uploads into a sqlserver database.
once a day I need to extract the data via tsql and email the output in html format.
I have a working script, but I want to highlight the first Row, but I cant work out how/where to code for this
below is my script:
declare @body varchar(max)
-- Create the body
set @body = cast( (
select td = cast (Day as varchar (10)) + '</td>
<td>' + cast(Date as varchar (12)) + '</td>
<td>' + cast(Time as varchar (6)) + '</td>
<td align=center>' + cast(WeekNumber as varchar (3)) + '</td>
<td align=center>' + cast(Data_Size_MB as varchar (10)) + '</td>
<td align=center>' + cast(Used_Data_MB as varchar (10)) + '</td>
<td align=center>' + cast(Data_Full_Pcnt as varchar (6)) + '</td>
<td align=center>' + cast(Log_Size_MB as varchar (10)) + '</td>
<td align=center>' + cast(Free_Log_MB as varchar (10)) + '</td>
<td align=center>' + cast(Log_Full_Pcnt as varchar (6))
from (
select Day = datename(dw,snapshot_Date),
Date = convert(varchar(19), Snapshot_Date, 106),
Time = substring(CONVERT(VARCHAR, snapshot_Date, 108),0,6),
WeekNumber = DATEPART(week, snapshot_Date),
Data_Size_MB = Data_Size_MB,
Used_Data_MB = Used_Data_MB,
Data_Full_Pcnt = Data_Full_Pcnt,
Log_Size_MB = Log_Size_MB,
Free_Log_MB = Free_Log_MB,
Log_Full_Pcnt = Log_Full_Pcnt,
snapshot_Date
from Ideal_Risk.dbo.Sybase_Disk_Space
where (datepart(hh, Snapshot_Date) = 12 and datepart(mm, Snapshot_Date) >= 58)
or (datepart(hh, Snapshot_Date) = 13 and datepart(mm, Snapshot_Date) <5)
and database_name='IDEAL'
) as d
order by snapshot_Date descfor xml path( 'tr' ), type ) as varchar(max) )
set @body = '<header><font size="6" color="Green"><b><u>Sybase Database disk usage statistics</u></b></font></header>'
+ '<p> </p>'
+ '<table cellpadding="2" cellspacing="2" border="1">'
+ '<tr>
<th>Day</th>
<th>Date</th>
<th>Time</th>
<th>WeekNumber</th>
<th>Data_Size_MB</th>
<th>Used_Data_MB</th>
<th>Data_Full_Pcnt</th>
<th>Log_Size_MB</th>
<th>Free_Log_MB</th>
<th>Log_Full_Pcnt</th>
</tr>'
+ replace( replace( @body, '<', '<' ), '>', '>' )
+ '</table>'
print @body
below is the current output :
Sybase Database disk usage statistics as at
Day Date Time WeekNumber Data_Size_MB Used_Data_MB Data_Full_Pcnt Log_Size_MB Free_Log_MB Log_Full_Pcnt Thursday 23 Feb 2017 13:00 9 118784.00 90875.99 76.51 1024.00 1015.47 0.83 Wednesday 22 Feb 2017 13:00 9 118784.00 90765.73 76.41 1024.00 1008.85 1.48 Tuesday 21 Feb 2017 13:00 9 118784.00 90636.79 76.30 1024.00 1001.83 2.17 Monday 20 Feb 2017 13:00 9 118784.00 90438.33 76.14 1024.00 1009.73 1.39 Sunday 19 Feb 2017 13:00 8 118784.00 90299.55 76.02 1024.00 1020.00 0.39 Saturday 18 Feb 2017 13:00 8 118784.00 90297.19 76.02 1024.00 1019.99 0.39 Friday 17 Feb 2017 13:00 8 118784.00 90265.84 75.99 1024.00 1016.24 0.76 Thursday 16 Feb 2017 13:00 8 118784.00 90133.98 75.88 1024.00 1004.76 1.88 Wednesday 15 Feb 2017 13:00 8 118784.00 89984.00 75.75 1024.00 1011.83 1.19 Tuesday 14 Feb 2017 13:00 8 118784.00 89825.19 75.62 1024.00 1008.42 1.52 Monday 13 Feb 2017 13:00 8 118784.00 89664.79 75.49 1024.00 1010.99 1.27 Sunday 12 Feb 2017 13:00 7 118784.00 90274.24 76.00 1024.00 1019.93 0.40 Saturday 11 Feb 2017 13:00 7 118784.00 90272.05 76.00 1024.00 1019.99 0.39 Friday 10 Feb 2017 13:00 7 118784.00 90261.56 75.99 1024.00 1016.15 0.77 Thursday 09 Feb 2017 13:00 7 118784.00 90133.38 75.88 1024.00 1009.40 1.43 Wednesday 08 Feb 2017 13:00 7 118784.00 89989.14 75.76 1024.00 1009.38 1.43 Tuesday 07 Feb 2017 13:00 7 118784.00 89858.59 75.65 1024.00 1004.09 1.94 Monday 06 Feb 2017 13:00 7 118784.00 89707.38 75.52 1024.00 1006.06 1.75 Sunday 05 Feb 2017 13:00 6 118784.00 89550.29 75.39 1024.00 1019.25 0.46 Saturday 04 Feb 2017 13:00 6 118784.00 95983.12 80.80 1024.00 999.76 2.37 Friday 03 Feb 2017 13:00 6 118784.00 95971.10 80.79 1024.00 1014.48 0.93 Thursday 02 Feb 2017 13:00 6 118784.00 95844.68 80.69 1024.00 1009.73 1.39 Wednesday 01 Feb 2017 13:00 6 118784.00 95694.88 80.56 1024.00 1011.38 1.23 Tuesday 31 Jan 2017 13:00 6 118784.00 95444.92 80.35 1024.00 1009.36
also I dont understand why the last cell (or more) of the last line are missing
and why it has stopped at 31st Jan when there is data goign back to 1st Jan 2017
February 23, 2017 at 8:48 am
Is that the output from the "print @body" line? Because that will only print 4000 characters (unicode) / 8000 characters non-unicode. There's more about the limitations of the T-SQL PRINT command on MSDN.
As for the selective formatting of the first line - I assume you mean the first returned line of data? There are a couple of options:
1. change the SELECT statement that generates the data set so that it also returns a ROW_NUMBER or similar id field; put some additional code in the outer SELECT to check for that value being '1', and, if so, put in appropriate HTML formatting stuff. (If you're on SQL 2005, this might be a bit trickier to do...)
2. add another step to the REPLACE to look at the body text between the first <tr> / </tr> pair, again, adding formatting at this point.
Thomas Rushton
blog: https://thelonedba.wordpress.com
February 24, 2017 at 8:38 am
Thanks re number of characters.
as for the select - I have two select statements,
I tried adding
select td = Select Row_Number() Over(Order By snapshot_Date) % 1 As [TRRow]'</td>
<td>' cast (Day as varchar (10)) + '</td>>
but it didnt work
I got errors
Msg 156, Level 15, State 1, Server CIF-PROD-SQL01, Line 5 Incorrect syntax near the keyword 'Select'. Msg 102, Level 15, State 1, Server CIF-PROD-SQL01, Line 5 Incorrect syntax near ' '. Msg 156, Level 15, State 1, Server CIF-PROD-SQL01, Line 32 Incorrect syntax near the keyword 'as'.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply