TSQL output email in html format - how to highlight first row

  • rob_nye

    Mr or Mrs. 500

    Points: 544

    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 desc

    for 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>&nbsp;</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, '&lt;', '<' ), '&gt;', '>' )
         + '</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

  • Thomas Rushton

    SSC-Insane

    Points: 22635

    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

  • rob_nye

    Mr or Mrs. 500

    Points: 544

    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 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply