SQL Server Performance

change font and text size in the output

Discussion in 'SQL Server 2005 General DBA Questions' started by avipenina, Jan 21, 2010.

  1. avipenina New Member

    Hi,
    how i can change the output font and text size from this send email html results?
    THX
    (example html output from BOL)
    DECLARE @tableHTML NVARCHAR(MAX) ;

    SET @tableHTML =
    N'<H1>Work Order Report</H1>' +
    N'<table border="1">' +
    N'<tr><th>Work Order ID</th><th>Product ID</th>' +
    N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
    N'<th>Expected Revenue</th></tr>' +
    CAST ( ( SELECT td = wo.WorkOrderID, '',
    td = p.ProductID, '',
    td = p.Name, '',
    td = w:confused:rderQty, '',
    td = wo.DueDate, '',
    td = (p.ListPrice - p.StandardCost) * w:confused:rderQty
    FROM AdventureWorks.Production.WorkOrder as wo
    JOIN AdventureWorks.Production.Product AS p
    ON wo.ProductID = p.ProductID
    WHERE DueDate > '2004-04-30'
    AND DATEDIFF(dd, '2004-04-30', DueDate) < 2
    ORDER BY DueDate ASC,
    (p.ListPrice - p.StandardCost) * w:confused:rderQty DESC
    FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

    EXEC msdb.dbo.sp_send_dbmail @recipients='danw@Adventure-Works.com',
    @subject = 'Work Order List',
    @body = @tableHTML,
    @body_format = 'HTML' ;
  2. preethi Member

    I use something like this:

    DECLARE @tableHTML nvarchar(max)
    DECLARE @Sub nvarchar(1000);
    SET @sub ='Work Order List - ' +CONVERT(varchar(30), GETDATE(), 100)
    SET @tableHTML =
    N'<html><head></head><body><H1>Work Order Report</H1>' +
    N'<table border="1">' +
    N'<tr><th>Work Order ID</th><th>Product ID</th>' +
    N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
    N'<th>Expected Revenue</th></tr>'
    SELECT @tableHTML = @tableHTML +N'<tr><td>'+Convert(nvarchar(max), wo.WorkOrderID)+N'</td>'
    +N'<td>'+ CONVERT(nvarchar(max),p.ProductID)+N'</td>'
    +N'<td>'+ CONVERT(nvarchar(max),p.Name)+N'</td>'
    +N'<td><b>'+ CONVERT(nvarchar(max),w:confused:rderQty)+N'</b></td>'
    +N'<td><font color ="red">'+ CONVERT(nvarchar(max),wo.DueDate)+N'</font></td>'
    +N'<td>'+ CONVERT(nvarchar(max),(p.ListPrice - p.StandardCost) * w:confused:rderQty)+N'</td></tr>'
    FROM Production.WorkOrder as wo
    JOIN Production.Product AS p
    ON wo.ProductID = p.ProductID
    WHERE DueDate > '2004-04-30'
    AND DATEDIFF(dd, '2004-04-30', DueDate) < 2

    SELECT @tableHTML = @tableHTML+ N'</table></body></html>'
    Select @tableHTML, @sub


    EXEC msdb.dbo.sp_send_dbmail @recipients ='user@domain.com',
    @subject = @sub,
    @body = @tableHTML,
    @importance ='High',
    @body_format = 'HTML' ;

    This gives additional functionality that you can add formatting based on values too.
    Hope this helps.
  3. avipenina New Member

    Hi,
    Thanks for answer.
    can you show me how to set it up in this example?
    declare
    @report_info nvarchar (max)SET
    @report_info =N
    '<br><font color="black" size="4" face="david">Report Info</font>' +N
    '<br><table border="2">' +N
    '<th><font color="black" size="3" face="david">Name</font></th>' +N
    '<th><font color="black" size="3" face="david">Value</font></th>' +CAST ( ( select td = NAME, '',td = VALUE,'' from msys_monitor.dbo.TBL_REPORT_INFO
    FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +N
    '</table>' ;
  4. preethi Member

    Now you are asking me to catch the fish instead of teach you to catch the fish [:(]
    1. Keep your set statement and Select Statement separately.
    2. Remove the FOR XML part from your SELECT Statement.
    3. Change your select statement to return a string (convert all values to nvarchar(max)
    4. Enclose the columns with <td> and </td>
    5. Enclose all rows with <tr> and <tr>
    6. Add the results of SELECT statement to the variable
    7. Add the bottom part to the variable -- In your case "<table>"
    It should work.
    declare @report_info nvarchar (max)
    SET @report_info =
    N'<br><font color="black" size="4" face="david">Report Info</font>' +
    N'<br><table border="2">' +
    N'<th><font color="black" size="3" face="david">Name</font></th>' +
    N'<th><font color="black" size="3" face="david">Value</font></th>' SELECT @report_info =@report_info + '<tr><td>' + NAME + '</td><td>' + VALUE,'' from msys_monitor.dbo.TBL_REPORT_INFO
    SELECT @report_info =@report_info + N'</table>' ;
    Note that I do not have your table with me to check for any syntax errors.

Share This Page