Archive for September, 2011

Using database mail in sql server 2008

Previously, in one my threads I have explained how to configure database mail for various service providers [Google, Yahoo, Live, etc]. Now letโ€™s see where this database mail can be used in a highly effective manner.

Following are the areas where we can use database mails,

  • During ETL load โ€“ for sending the load status
  • During Trigger on Insert, Update and Delete

For Delivering Status:
See the following t-sql code where i have created a table variable holding some sample data [think of an data in some ETL logging process]. For starting, it is now simple with package name and its status

This code will mail the status of load [data from table variable] to the respective mail ids mentioned in @recipients parameter. The same can be implemented in ETL package with an SQL task. I will post such a example in my future threads,

To configure the SQL Server database mail, please follow the steps in my other thread which is as follows,

DECLARE @LoadStatus Table (PackageName varchar (50), Result varchar( 50))
SELECT 'Package1' , 'Success' union
SELECT 'Package2' , 'Failed' union
SELECT 'Package3' , 'Inprogress' union
SELECT 'Package4' , 'Failed' union
SELECT 'Package5' , 'Success' union
SELECT 'Package6' , 'Success'

SELECT * FROM @LoadStatus

declare @tableHTML nvarchar (max)
SET @tableHTML =
                                     N'<H3>Load Status</H3>' +
                                     N'<table border="1">' +
                                     N'<tr><th>PackageName</th>' +
                                     N'<th>Result</th></tr>' +
                                     CAST ((
                                     case t. Result
                                     WHEN 'failed' then 'Red'
                                     WHEN 'Success' THEN 'Lime'
                                     else 'orange' end AS [@bgcolor] ,
                                     td = PackageName,         '',
                                     td = result, ''
                                     FROM (SELECT Packagename,result from @Loadstatus) t
                                                  ORDER BY Packagename  ASC
                                                  FOR XML PATH( 'tr'), TYPE
                                      )AS NVARCHAR( MAX) ) +

                                     N'</table>' ;

---PRINT @tableHtml

EXEC msdb. dbo.sp_send_dbmail

                            @profile_name='profile-A' ,
                              @recipients ='*****',
                              @from_address = '*****' ,
                              @subject = 'Load Status' ,
                              @body = @tableHTML,
                              @body_format = 'HTML' ;


You will receive an email like the one below,











Happy Querying ๐Ÿ˜‰

%d bloggers like this: