none
How to add HTML color tag/code in SQL 2005 HTML Report

    Question

  • Guys

    I have created HTML (output)  job in SQL 2005 I would like to add Background color for particular column upon condition any one please help me out on this.

    This is the procedure I have created :

    Create

     

    Proc SQLMorningChk_Proc

    as

     

    declare

     

    @tableHTML NVARCHAR(MAX)

     

    ,@email_rec varchar(200);

    select

     

    @email_rec = rtrim(Emaillist) from Email_List

     

    SET @tableHTML =

    N

    '<H1>SQL Server Morning Check Report </H1>' +

    N

    '<table border="1">' +

    N

    '<tr><th>DBName</th>' +

    N

    '<th>ServerName</th>' +

    N

    '<th>StatusNow</th>' +

    N

    '<th>Status_Last_48hrs</th>' +

    N

    '<th>LastBackup</th>' +

    N

    '<th>Last48hrsBKP</th>' +

    N

    '<th>FreeSpacePct</th>' +

     

    CAST ( ( SELECT td = DBName, '',

    td

    = ServerName, '',

    td

    = Status_Now, '',

    td

    = Status_Last_48hrs, '',

    td

    = FinishDate, '',

    td

    = Last48hrsBKP, '',

    td

    = FreeSpacePct, ''

     

    FROM SRVR23007.Master.dbo.SQLMrgBackupHistory where DBName not in ('Master','msdb','model')

     

    FOR XML PATH('tr'), TYPE

     

    ) AS NVARCHAR(MAX) ) +

    N

    '</table>' ;

    EXEC

     

    msdb.dbo.sp_send_dbmail

    @profile_name

    = 'SQL MAIL',

    @recipients

    ='xyx.com',

    @subject

    = 'SQL Server Morning Check Report',

    @body

    = @tableHTML,

    @body_format

    = 'HTML' ;

    The output is fine Iam getting what i exepcted but would like to add background color,

     like from my procedure

    IF td = FreeSpacePct > 10% It should Display in Green or should be in Red. Iam not sure where/how to add HTML Color HEX Codes.

    Please Help me out on this..

    Thanks
    NAG


    Nag Pal
    Tuesday, July 14, 2009 6:57 AM

All replies

  • I don't think there are any conditional statements directly in HTML.
    Either you have to do this check in the  SQL or you could apply a style sheet on the generated HTML (not sure if this is doable from within SQL Server).

    Anyway, for setting the background color, this is done with the bgcolor property.
    For example:

    <table>
    <tr>
      <td bgcolor=#700000">Column One</td>
      <td bgcolor=#00FF00">Column Two</td>
    </tr>
    </table>

    For more about colors, see for example:
    http://w3schools.com/html/html_colors.asp

    HTH
    //Michael

    This posting is provided "AS IS" with no warranties.
    Tuesday, July 14, 2009 10:21 AM
  • Hi Michael

    Thanks a lot for your Reply.
    I tried to use this in SQL Script and it is just printing with
    &lt;td BGCOLOR=#00FF00"&lt;/td&gt

    This is how iam giving in the select stmt

    td

    = case When Status_Now='Good' then '<td BGCOLOR=#00FF00"</td>' end,

    and getting    &lt;td BGCOLOR=#00FF00"&lt;/td&gt  not sure where iam doing mistake. :(

    Regards
    Nag


    Nag Pal
    Tuesday, July 14, 2009 11:11 AM
  • Hi Nag Pal

    This happens because you are outputing the text as XML (FOR XML PATH).
    In XML certain charachters are not allowed under certain circumstances and they are then replaced.
    In this case, < is replaced by &lt; (meaning 'less than') and > is replaced by &gt; (meaning 'greater than').

    For example:
      select '<table><tr><td bgcolor=#700000">Column One</td></tr></table>' as html
    will give: 
      <table><tr><td bgcolor=#700000">Column One</td></tr></table>
    and
      select '<table><tr><td bgcolor=#700000">Column One</td></tr></table>' as html for xml path('x')
    will give:
      <x><html>&lt;table&gt;&lt;tr&gt;&lt;td bgcolor=#700000"&gt;Column One&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;</html></x>

    This is correct in terms of XML, see for example:
    http://www.w3schools.com/xmL/xml_syntax.asp

    Unfortunately I do not know enough of SQL Server to tell you how to keep these chars when outputting the text as XML.

    //Michael


    This posting is provided "AS IS" with no warranties.
    Tuesday, July 14, 2009 12:29 PM
  • Sadly, the only place that these characters are allowed in XML is in a CDATA section and that would not merge well with the intended display plan for this data.  It would be significantly easier to format the data on the client side (ASP page, etc.) by inserting the desired tags into the XML retrieved from the server than to require the server to generate the page in its entirety.

    Thanks,

    John
    This post is provided 'as is' and confers no express or implied warranties or rights.
    Tuesday, July 14, 2009 6:13 PM
    Moderator
  • Hi Michael

    Thanks for you inputs.. I managed to get what i want i simply replace the output varilable with '<' and '>'

    **************

    td

    = case when Status_now='Good' then '<font color="green">' + Status_now + '</font>' Else '<font color="red">' + Status_now + '</font>' end, '' ,

    and once complete with all the column's I just replace with

    Set

     

    @tableHTML = replace(replace(@tableHTML, '&lt;', '<'), '&gt;', '>')

     

    + '</table>'

    This is Working fine for me....Thanks a lot for your help.

    Regards
    Nag


    Nag Pal
    Wednesday, July 15, 2009 4:44 AM
  • Hi Nag Pal,

    Why don't you use Reporting Services to create report?  There you can easily apply such conditions and also can create subscriptions to send mails.
    Mangal Pardeshi BI
    SQL With Mangal
    Wednesday, July 15, 2009 5:58 AM
    Moderator
  • Hi Mangal

    Iam not much familier with Reporting Services I will try to learn quickly . Please send me some good link to learn from scratch.
    Thanks....

    Regards
    Nag
    Nag Pal
    Wednesday, July 15, 2009 6:23 AM
  • Wednesday, July 15, 2009 7:06 AM
    Moderator