Friday, August 24, 2012

send SQl DataBase Mail Out put in HTML Formate 

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
   N'<head>' +
    N'<style type="text/css">h2, body {font-family: Arial, verdana;} table{font-size:11px; border-collapse:collapse;} td{background-color:#F1F1F1; border:1px solid black; padding:3px;} th{background-color:#99CCFF;}</style>' +
   N'<h2><font color="#0000ff" size="4">Work Order Report</font></h2>' +   
   N'</head>' +
N'<body>' +
N' <hr> ' +
N' ' +
   
    
    N'<table border="1">' +
    N'<tr><th>Work Order ID</th><th>Product ID</th>' +
    N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
    N'<th>Expected Revenue</th>
    
    </tr>' +
    CAST ( ( SELECT td = wo.WorkOrderID,       '',
                    td = p.ProductID, '',
                    td = p.Name, '',
                    td = wo.OrderQty, '',
                    td = wo.DueDate, '',
                    td = (p.ListPrice - p.StandardCost) * wo.OrderQty
              FROM AdventureWorks2008R2.Production.WorkOrder as wo
              JOIN AdventureWorks2008R2.Production.Product AS p
              ON wo.ProductID = p.ProductID
              WHERE DueDate > '2006-04-30'
                AND DATEDIFF(dd, '2006-04-30', DueDate) < 2 
              ORDER BY DueDate ASC,
                       (p.ListPrice - p.StandardCost) * wo.OrderQty DESC
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail @recipients='xyz@abc.com',
    @profile_name = 'SQl',
    @subject = 'Work Order List',
    @body = @tableHTML,
    @body_format = 'HTML' ;
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)