Home Forums SQL Server 2005 T-SQL (SS2K5) SQL DB Email sending,HTML body formatting from two tables RE: SQL DB Email sending,HTML body formatting from two tables

  • I actually figured it out a moment after posting. For anyone in a similar sitation I did the following:

    DECLARE @tableHTMLA VARCHAR(MAX) ;

    SET @tableHTMLA =

    N'<H1>PLACE TABLE HEADER HERE</H1>' +

    N'<table border="1">' +

    N'<tr><th>COLUMN 1 HEADER</th>' +

    N'<th>COLUMN 2 HEADER</th>' +

    N'<th>COLUMN 3 HEADER</th> </tr>' +

    CAST ( ( SELECT td = COLUMN1 0 END), ' ',

    td = COLUMN2, ' ',

    td = COLUMN3, ' '

    FROM DATABASE_Name..Table_Name

    FOR XML PATH('tr'), TYPE

    ) AS NVARCHAR(MAX) ) +

    N'</table>' ;

    DECLARE @tableHTMLB VARCHAR(MAX) ;

    SET @tableHTMLB =

    N'<H1>PLACE TABLE HEADER HERE</H1>' +

    N'<table border="1">' +

    N'<tr><th>COLUMN 1 HEADER</th>' +

    N'<th>COLUMN 2 HEADER</th>' +

    N'<th>COLUMN 3 HEADER</th> </tr>' +

    CAST ( ( SELECT td = COLUMN1 0 END), ' ',

    td = COLUMN2, ' ',

    td = COLUMN3, ' '

    FROM DATABASE_Name..Table_Name

    FOR XML PATH('tr'), TYPE

    ) AS NVARCHAR(MAX) ) +

    N'</table>' ;

    DECLARE @body2 NVARCHAR(MAX) ;

    SET @body2 = @tableHTMLA + @tableHTMLB ;

    EXEC msdb.dbo.sp_send_dbmail @recipients='Someone@yahoo.com',

    @subject = 'Place Subject Here',

    @body = @body2,

    @body_format = 'HTML',

    This may be a very round about way to do this ut it got me the exact results I needed so I am happy.