Email sending Format in sql server

Type 1

 

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'test',

    @recipients = 'test@mail.com',

    @body = 'The stored procedure finished successfully.',

    @subject = 'Automated Success Message' ;

 

Type 2

 

EXEC msdb.dbo.sp_send_dbmail 

    @profile_name = 'test',

    @recipients =  'test@mail.com',

    @query = 'SELECT COUNT(*) FROM Database.dbo.tablename

                  WHERE column1 > ''2004-04-30''

                  AND  DATEDIFF(dd, ''2004-04-30'', column2) < 2' ,

      @subject = 'Work Order Count',

    @attach_query_result_as_file = 1 ;

 

Type 3

 

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =

    N'<H1>Work Order Report</H1>' +

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

    N'<tr><th>Column name1</th><th> Column name2</th>' +

    N'<th> Column name3</th><th> Column name4</th><th> Column name5</th>' +

    N'<th> Column name6</th></tr>' +

    CAST ( ( SELECT td = wo.column1,       '',

                    td = p.column2, '',

                    td = p.column3, '',

                    td = wo.Column4, '',

                    td = wo.Column5, '',

                    td = (p.column6 - p.column4) * wo.Column2

              FROM Database.dbo.tablename1 as wo

              JOIN Database.dbo.tablename2  AS p

              ON wo.column1 = p.column1

              WHERE condition

              ORDER BY column1 ASC,

FOR XML PATH('tr'), TYPE 

    ) AS NVARCHAR(MAX) ) +

    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail 

    @recipients =  'test@mail.com',

    @subject = 'Work Order List',

    @body = @tableHTML,

    @body_format = 'HTML' ;