SQL Server Performance

HTML email

Discussion in 'SQL Server 2005 General Developer Questions' started by gopal, Jul 11, 2011.

  1. gopal Member

    I am trying to send the email by HTML format. I got the email working but I couldn't format the output. I would like the output to be centered. Here is my code:

    CREATE PROC [dbo].[email_test]
    @start_dt datetime,
    @end_dt datetime,
    @opo varchar(10)

    as
    SET NOCOUNT ON

    TRUNCATE TABLE email_test

    INSERT INTO email_test
    EXEC dataload.dbo.call_main_proc @start_dt, @end_dt

    DECLARE @tableHTML NVARCHAR(MAX)
    DECLARE @subject varchar(70)

    SET @tableHTML =
    N'<table border="1">' +
    N'<align="Center">' +
    N'<th>OPO</th><th>Col1</th>' +
    N'<th>Col2</th><th>Col3</th><th>Col4/Time</th>' +
    N'<th>Col5</th><th>Col6</th><th>Col7</th>' +
    N'<th>Col8</th><th>Col9</th><th>Col110</th>' +
    N'<th>Col11</th><th>Col12</th><th>Col13</th>' +
    N'<th>Col14</th><th>Col15</th>' +
    N'<th>Col16</th></tr>' +

    CAST ((
    SELECT
    td = [col1], '',
    td = [col2], '',
    td = [col3], '',
    td = [col4], '',
    td = [col5], '',
    td = [col6], '',
    td = [col7], '',
    td = [col8], '',
    td = [col9], '',
    td = [col10], '',
    td = [col11], '',
    td = [col12], '',
    td = [col13], '',
    td = [col14], '',
    td = [col15], '',
    td = [col16], ''

    FROM email_test WITH (NOLOCK)
    FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

    SELECT @subject = 'Monthly Report : ' + convert(varchar(10), @start_dt, 103) + ' - ' + convert(varchar(10), @end_dt, 103)

    EXEC msdb.dbo.sp_send_dbmail
    @recipients='test@email.com',
    @subject = @subject,
    @body = @tableHTML,
    @body_format = 'HTML' ;

    I was able to center the header but I was unable to center the results. Can someone help on this please?

    Thanks.
  2. FrankKalis Moderator

    Which output should be centered? If I see this correctly, currently the table is centered, or do you want the content in the cells to be centered?

Share This Page