How To Send Employees Birthday EMail From SQL Server

Introduction

SQL Server can be used to create and run jobs automatically. One of my tasks was to create a job in SQL Server that will send emails to all company employees on their birthdays. In this post, let's see how you can create and schedule a job in to send employee birthday emails to SQL Server.

Once you learn that, you can configure the email you like and send it for whatever reason. It could also be a weekly newsletter to your company's employees. 

There are three steps required to set up and send emails in SQL Server.

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

Create a Profile and Account in SQL Server

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

• The account name is Database Mail (happybirthday)
• The description is an optional
• Email Address - [email protected]
• Display Name - happybirthday
• Reply Email - It can be blank or use the same email as above.
• Server Name - localhost. This is an SMTP server.
• Port Number - server port number to be used is the default port number is 25.
• Secure Connection - We have to select an SSL connection,n as shown in the picture for mail.

Configure Email

After successfully creating the Account and Profile, 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

Send Email through Stored Procedure

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

We must 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 EMPLOYEE BIRTHDAY. 
 ============================================= 
*/

ALTERPROCEDURE [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   [email protected]
      [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 [email protected]'           
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 + 1 
END 
END 
END 

Start Birthday Job

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

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.:

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

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.

Job added

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

Job schedule added

Job successfully added.

Summary

In this post, we learned how to schedule a job to automatically send emails to some email accounts in a database. Similarly, you can use this approach to send emails from a SQL Server database without writing a single line of code. 

If you want to send emails from code, here is an article: Sending Emails In C# Application