Reader Level:
ARTICLE

Employee's Birthday Mail by SQL Server

Posted by Shivom Agarwal Articles | SQL December 18, 2012
In this article, I will explain the three basic thingss to do to send mail in SQL Server.
  • 2
  • 0
  • 8554

Firstly Configure SQL Mail in Local Database

In order to send mail by SQL Server 2008, there are 3 basic things to do:

  1. Create Profile and Account
  2. Configure Email
  3. Send Email

Step 1: Create Profile and Account

You need to create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail context menu of the Database Mail node in Management Node.

Birthday-Mail-by-SQL-Server1.jpg

Birthday-Mail-by-SQL-Server2.jpg

Birthday-Mail-by-SQL-Server3.jpg

Account name is Database Mail (happybirthday)
Description is an optional
E-Mail Address: xxx@xxx.xx

Display Name: happybirthday

Reply Email: It can be blank or we can use the same email as above.

Server Name: localhost. This is SMTP server.

Port Number: server port number to be used is default port number is 25.

Secure Connection: We have to select a SSL connection as shown in the picture for mail.

Birthday-Mail-by-SQL-Server16.jpg

Birthday-Mail-by-SQL-Server17.jpg

Birthday-Mail-by-SQL-Server5.jpg

Birthday-Mail-by-SQL-Server6.jpg

Step 2: Configure Email

After the Account and the Profile are created successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter through the sp_configure stored procedure, as shown here:

USE msdb
GO
sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO

Birthday-Mail-by-SQL-Server7.jpg

Step 3: Send Email

After all configurations are done, we are now ready to send the employee's birthday mail through job schedules.  

We need to execute a stored procedure for the employee's birthday, "Get_birthday_emp".

Provide the required stored procedure and job step as shown below:

/*
============================================= 
Author:  xxxxxxx
 Create date: xxxx-xx-xx 
 Description: This procedure is used for EMPLYEE BIRTHDAY . 
 ============================================= 
*/

ALTER PROCEDURE [dbo].[Get_birthday_emp] 
AS 
BEGIN 
DECLARE @DATE varchar(20) 
SET @DATE =   right(cast(GETDATE()as DATE),5)
/*
CREATE TABLE EmplyeeDetail
(
       [Name] nvarchar(50) ,-- name xxxxx
      [EmailId] nvarchar(100) ,-- emailid   xxxxx@yy.zz
      [DOB] date --  date format  yyyy-mm-dd
      )
*/   

CREATE TABLE #SS 
( 
      CON               INT IDENTITY(1,1), 
      NAME        VARCHAR(50), 
      EmailId           VARCHAR(70), 
      DOB               varchar(20) 
) 
INSERT INTO #SS
(NAME,EmailId,DOB) 
(SELECT NAME,EmailId,right(cast(DOB as DATE),5) FROM EmplyeeDetail 
WHERE right(cast(DOB as DATE),5) =@DATE) 
  select * from #SS
DECLARE @ADMIN VARCHAR(100) 
            SET @ADMIN =xx.xx@yyy.zz'           
DECLARE @NAME VARCHAR(100)    
DECLARE @EMAILID VARCHAR(100) 
DECLARE @I INT 
            SET @I = 1                
DECLARE @COUNT  INT 
      SELECT @COUNT = COUNT(CON) FROM #SS 
      WHILE (@I <= @COUNT) 
      BEGIN   
 if (@COUNT>=1) 
  BEGIN
            SELECT @NAME=NAME, @EMAILID=EmailId FROM #SS WHERE CON = @I 
            SELECT @NAME,@EMAILID     
  /* HTML table for birthday person  */
  DECLARE @birthdaytableHTML  NVARCHAR(MAX);
SET @birthdaytableHTML =
'<table width="100%" cellpadding="15" cellspacing="15" bgcolor="#dcdcdc"> 
 <tr> 
     <td width="600px" align="center">           
         <table width="600px" border="0" cellspacing="0" cellpadding="0" bgcolor="#ffffff" align="center">            
  <tr> 
    <td> 
    <table width="600" border="0" cellspacing="0" cellpadding="0">     
        <tr>              
          <td align="left" valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="0"> 
               <tr> 
                <td height="11" style="font-size:0; line-height:0;">&nbsp;</td> 
               </tr> 
               <tr> 
                <td  height="45" style="font-size:22px; color:#0270bf;text-align:center; text-transform:uppercase; font-family:Arial, Helvetica, sans-serif; padding:0 0 0 10px;">Wishing you a very Happy Birthday '+ @NAME+' </td> 
               </tr> 
              <tr> 
                <td height="10">&nbsp;</td> 
               </tr> 
              <tr> 
               <td> 
    <img src="http://sms.latestsms.in/wp-content/uploads/birthday-scraps3.jpg"" />
                </td> 
              </tr> 
               <tr><td height="20"></td></tr> 
             </table></td> 
        </tr> 
       </table> 
      </td> 
  </tr> 
  <tr> 
    <td height="40"><table width="600" border="0" cellspacing="0" cellpadding="5">            
        <tr> 
          <td style="font-size:11px; color:#0270bf; font-family:Arial, Helvetica, sans-serif; text-align:center; font-size:22px">By MCN family</td> 
        </tr> 
       </table></td> 
  </tr> 
</table>   
         </td> 
    </tr>   
</table>'

  /* HTML table for reminder HR person  */

  DECLARE @RemindertableHTML  NVARCHAR(MAX);
SET @RemindertableHTML =
'<table width="100%" cellpadding="15" cellspacing="15" bgcolor="#dcdcdc"> 
 <tr> 
     <td width="600px" align="center"> 
         
         <table width="600px" border="0" cellspacing="0" cellpadding="0" bgcolor="#ffffff" align="center"> 
  <tr> 
    <td> 
    <table width="600" border="0" cellspacing="0" cellpadding="0"> 
        <tr> 
          <td align="left" valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="0"> 
               <tr> 
                <td height="11" style="font-size:0; line-height:0;">&nbsp;</td> 
               </tr> 
               <tr> 
                <td  height="45" style="font-size:22px; color:#0270bf;text-align:center; text-transform:uppercase; font-family:Arial, Helvetica, sans-serif; padding:0 0 0
10px;">Today is '
+ @NAME+'  Birthday </td> 
               </tr> 
               <tr> 
                <td height="10">&nbsp;</td> 
               </tr> 
              <tr> 
               <td> 
       </table></td> 
  </tr> 
</table> 
         </td> 
    </tr> 
</table>'

  EXEC msdb.dbo.Sp_send_dbmail @profile_name='HappyBirthday',  
@recipients=@ADMIN,  
@subject='Reminder Mail' ,  
@body = @RemindertableHTML,
@body_format= 'HTML'
exec msdb.dbo.sp_send_dbmail
@profile_name = 'HappyBirthday',
@recipients = @EMAILID,
@subject = '  Happy Birthday. ',
@body = @birthdaytableHTML,
@body_format= 'HTML'
    SELECT @I = @I +
END 
END 
END 

Start Birthday Job

Step  1: Ensure that SQL Server Agent is up and running. You can see it below.

Birthday-Mail-by-SQL-Server8.jpg

Click the YES button.

Step 2: Right-click on SQL Server Agent. You will see the option for "New" there. Add the Job as in the following.:

Birthday-Mail-by-SQL-Server9.jpg

Step 3: The New Job popup will appear. Specify the name of the job.

Birthday-Mail-by-SQL-Server10.jpg

Step 4: Click next on the "Steps" in the left menu. A SQL job can contain one or more steps. A step might be simply a SQL statement or a stored procedure call. Add your step here.

Birthday-Mail-by-SQL-Server12.jpg

Job added:

Birthday-Mail-by-SQL-Server13.jpg

Step 5: Click next on the "Schedules" in the left menu. A SQL job can contain one or more schedules. A schedule is basically the time at which the sql job will run itself. You can specify recurring schedules also.

Birthday-Mail-by-SQL-Server14.jpg

Job schedule added:

Birthday-Mail-by-SQL-Server15.jpg

Job successfully  added.

COMMENT USING

Trending up