Post reply

Formatting a email from a SQL job

  • May 18, 2015 at 9:01 am

    #317509

    Hello,

    I am trying to correct the formatting (so its aligned) on an email sent from one our jobs called: "Get Log File Size"

    Is there something in the code that I need to change?

    The query is:

    -- Start T-SQL

    USE msdb

    EXEC sp_send_dbmail

    @profile_name='RL1VMSQL03 profile',

    @recipients='email@email.com',

    @subject='Log File Sizes for RL1VMSQL03',

    @body='See details below:',

    @query='select LEFT(RTRIM([instance_name]),32) as [Database Name],RTRIM(cntr_value) as "Log File(s) Size (KB)"

    --,counter_name

    from sys.dm_os_performance_counters

    where counter_name like ''Log File(s) Size (KB)%''

    and instance_name not in (''_total'',''mssqlsystemresource'')

    order by cntr_value desc'

    -- End T-SQL

    The email looks like this:

    See details below:.

    DBNAME Log File(s) Size (KB)

    -------------------------------- ------------------------

    OptoMize_Reporting 6904824

    InfraLive 5957624

    tempdb 5199864

    NetPerfmon 4236280

    ConceptEvolution 4012024

    SolarWinds 3923960

    SharePoint_Config 3587064

  • May 18, 2015 at 10:08 am

    #1798694

    Bump

  • May 18, 2015 at 10:19 am

    #1798701

    DB mail has limited formatting options, if you want a pretty report you would be better off using something else.

  • May 18, 2015 at 11:20 am

    #1798733

    It can be done, but it's a bit of a pain to get working sometimes.

    Check out this link[/url] for an example of how to go about this.

    Cheers!

  • May 18, 2015 at 3:51 pm

    #1798857

    You can also get a decent example right out of "Books Online". Lookup sp_send_dbmail and look at the last example.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 19, 2015 at 5:16 am

    #1798963

    try this

    https://www.virtualobjectives.com.au/sqlserver/saving_to_html.htm

    it will help you

    step by step guidance for sending nice formatted html

  • May 19, 2015 at 7:49 am

    #1799023

    How about like this with a formatted table embedded in the body of the email:

    DECLARE @ResponseAddress nvarchar(100),

    @htmltext nvarchar(max),

    @title nvarchar(256)

    SET @ResponseAddress = 'email@email.com'

    -- Create the HTML Text for the body of the email.

    SET @htmltext =

    N'<H3 style="color:blue; font-family:verdana">See details below:</H3>' +

    N'<p align="left" style="font-family:verdana; font-size:8pt">' +

    N'<table border="3" style="font-size:8pt; font-family:verdana; text-align:left">' +

    N'<tr style="color:42426F; font-weight:bold">' +

    N'<th>DatabaseName</th>' +

    N'<th>Log File(s) Size (KB)</th></tr>' +

    ISNULL(CAST ( (

    SELECTtd = LEFT(RTRIM([instance_name]),32), '',

    td = RTRIM(cntr_value), ''

    FROM sys.dm_os_performance_counters

    WHERE counter_name like 'Log File(s) Size (KB)%'

    AND instance_name not in ('_total'',''mssqlsystemresource')

    ORDER BY cntr_value desc

    FOR XML PATH('tr'), TYPE

    ) AS NVARCHAR(MAX) ),'') +

    N'</table>'

    --Set the email title

    set @title = 'Log File Sizes for RL1VMSQL03'

    --Send the email

    EXEC msdb.dbo.sp_send_dbmail @recipients=@ResponseAddress,

    @subject = @title,

    @body = @htmltext,

    @body_format = 'HTML' ,

    @profile_name = 'SQLServer_Alerts'

    GO

    MCITP SQL 2005, MCSA SQL 2012

  • May 19, 2015 at 8:19 am

    #1799030

    Thank you so much! It works beautifully 🙂

Viewing 8 posts - 1 through 7 (of 7 total)

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