CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2008-r2
fdkgfosfskjdlsjdlkfsf
fdkgfosfskjdlsjdlkfsf

Reputation: 3303

With sp_send_dbmail, change font/size in results sent via email?

I have this simple query that sends an email with the SP results:

USE msdb
EXEC sp_send_dbmail
@profile_name = 'Mail list',
  @recipients = '[email protected]',
  @subject = 'query results',
  @body = 'Result from SELECT appended below.',
  @execute_query_database = 'DB',
  @query = 'exec usp_DisplayData'

Everything works correct, but I would like to make the text smaller without losing the column format that I get with a fixed-width font. Is this possible?

I was looking at using HTML, but the suggestions I've found don't seem to work with stored procedures (I have to use the actual query).

Also, I wanted to add a link to the body (with a href) instead of posting the really long URL, and I suppose I can only do that if the email is html-formatted.

Thanks.

Upvotes: 0

Views: 4434

Answers (2)

Laughing Vergil
Laughing Vergil

Reputation: 3756

You basically will need to change the @query parameter to something like:

@query = 'exec usp_DisplayData_HTML'

... and then write the HTML formatted output version of the usp_DisplayData proc. Sorry, but unless you go for some custom solution, that's all there is.

Upvotes: 1

Russell Fox
Russell Fox

Reputation: 5427

The query is run in a separate session so I suspect any html formatting that's applied to the message body will not apply to the data set. However, if you set the body format to html (instead of text) then you can alter the stored procedure to return valid html instead of raw data. This is a giant pain in the rear because you have to convert all numbers and dates into strings, but it's possible: sample below.

A better method would be to create an SSRS report and send it out via subscription. Set the output format to "MHTML (web archive)" and it will be embedded as the body of the email.

    DECLARE @body VARCHAR(MAX)

--  Set up CSS formatting for the email body:   
    SELECT @body = 
    '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml" >
    <head> <title>Tolerance check on completed import</title>
    <meta charset="UTF-8" />
        <style type="text/css">

            body {
                margin-left:20px;
                font-size: 70%;
                font-family: Arial, Verdana, sans-serif;
                }

            table {
                border-collapse:collapse;
                border: none;
                }'
--etc...

    --  Start a table:
    SELECT @body += 
        '<h5>Import Information</h5>
        <table style="margin-top: 0;">
        <tr>
        <th>ImportID</th>
        <th>State - Config</th>
        <th>Frequency</th>
        <th>Imported</th>
        </tr>'

    -- Add detail rows to the table with a SELECT statement
    SELECT DISTINCT @body += 
        '<tr><td>' + CAST(t1.ID AS VARCHAR(10)) + '</td>
        <td>' + t2.StateCode + ' - ' + CAST(t1.StateConfigID AS VARCHAR(10)) + '</td>
        <td>' + @Freq + '</td>
        <td>' + CONVERT(NVARCHAR, t1.CreateDate , 101) + '</td></tr>'
    FROM dbo.Imp t1
        JOIN Cfg t2
            ON t1.CfgID = t2.ID
    WHERE t1.ID = @ID

    SELECT @body += '</table>'

    RETURN @body

Upvotes: 2

Related Questions