Employee's Birthday Mail by SQL Server

Shivom Agarwal
Dec 18, 2012
30.1k
0
3
Article

Firstly Configure SQL Mail in Local Database

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

Create Profile and Account
Configure Email
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.

Account name is Database Mail (happybirthday)
Description is an optional
E-Mail Address: [email protected]

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.

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

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 [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;"> </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"> </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;"> </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"> </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 basically the time at which the sql job will run itself. You can specify recurring schedules also.