Sending automatic birthday E-Mail From Sql server no working

my Stored Procedure

-- =============================================

-- Author: Raviraj.M

-- Create date: 5th Dec 2013

-- Description: Automatic Email Serves

-- =============================================

ALTER PROCEDURE [dbo].[AutomatcEmailServer]

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @DATE varchar(20)

SET @DATE = right(cast(GETDATE()as DATE),5)

DECLARE @MinId INT

SET @MinId = (select MIN(Id) as id FROM StudentInfo

WHERE right(cast(DateOfBirth as DATE),5) =@DATE)

DECLARE @MaxId INT

SET @MaxId=(SELECT MAX(Id) as id FROM StudentInfo

WHERE right(cast(DateOfBirth as DATE),5) =@DATE)

DECLARE @ADMIN VARCHAR(100)

SET @ADMIN ='[email protected]'

DECLARE @NAME VARCHAR(100)

DECLARE @EMAILID VARCHAR(100)

WHILE (@MinId<=@MaxId)

BEGIN

DECLARE @IsNull NVARCHAR(50)

SELECT @IsNull=Id FROM StudentInfo WHERE Id=@MinId and right(cast(DateOfBirth as DATE),5)=@DATE

IF (@IsNull!='')

BEGIN

SELECT @NAME=StudentName,@EMAILID=EmailId FROM StudentInfo WHERE Id=@MinId

/* HTML table for birthday person */

DECLARE @birthdaytableHTML NVARCHAR(MAX);

SET @birthdaytableHTML =

Wishing you a very Happy Birthday '+ @NAME+'

By MCN family

/* HTML table for reminder HR person */

DECLARE @RemindertableHTML NVARCHAR(MAX);

SET @RemindertableHTML =

10px;">Today is '+ @NAME+' Birthday

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 @MinId = @MinId + 1

END

ELSE

BEGIN

SELECT @MinId = @MinId + 1

END

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

vaibhav bhargav12y ago
Just check your profile name...Configure it properly using sysmail_add_profile_sp
0
Jignesh Trivedi12y ago
hi,
It might problem of database mail server, SMTP or fire wall.
Please refer
http://technet.microsoft.com/en-us/library/ms190630(v=sql.105).aspx
hope this will help you.
0