duplicate emails going out from stored procedure

[SiobhanP]

I have a series of stored procedures that grab a list of everyone who has an evaluation coming due in a specific time from the current date, and it sends the person and their supervisor an email. The code uses a temp table populated from a different stored procedure, and then uses a cursor for looping through to send out the emails.

It sends one email to the employee, one to the supervisor, fine and dandy. We have a new HR person, and she would like to receive a copy of the email sent out to the employee, so she's got an on going list of who has received them, for her own nefarious purposes. I added a single block of exactly the same code I use for the employee/supervisor emails, and when it runs the HR person gets -three- copies of the email, not just the one I want her to. I am at a loss for figuring out why on earth this is happening. I was hoping that someone here might be able to help me figure out what's wrong.

I'm attaching the code for the stored procedure with identifying information removed, but everything else intact. (never tried to do this before, hope it works)

 

[djj55]

Why not send one email and cc HR and supervisor?

Sorry not sure of why the problem.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[SiobhanP]

Because HR wants different text to go to the different parties involved. Otherwise that would be a lovely, elegant solution, but I couldn't get HR to accept it.

 

[SiobhanP]

Well, I'd prefer to know why it keeps doing what it's doing for future reference, but that doesn't appear to be in the cards.

So I'm just going to add her as a CC on the one that goes to the employee. Thanks, djj55 for reminding it was a possibility (I coded this originally two years ago, and CCs had been shot down, so I didn't even consider it a possible solution when HR decided they wanted a copy )

 

[djj55]

Sorry I could not help but I thought someone would jump in.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[sparkbyte]

Sorry, but could you paste you uSP into a reply? I work behind a firewall that blocks all online storage sites and cannot get to your code.



Thanks

John Fuhrman

 

[SiobhanP]

USE

---

GO
/****** Object: StoredProcedure [dbo].[sp_SendOutEmailForEvalDue1Month] Script Date: 02/01/2011 12:24:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/* =============================================
Author: SP
Create date: 2/5/2009
Description: processes the list of people who have an evaluation due in one month and sends out email notifications
============================================= */

ALTER PROCEDURE [dbo].[sp_SendOutEmailForEvalDue1Month]
AS
BEGIN

Declare @EmpFirstName as nvarchar(100)
Declare @EmpLastName as nvarchar(100)
Declare @EmpEmail as nvarchar(100)
Declare @SupEmail as nvarchar(100)
Declare @EmpBody as nvarchar(max)
Declare @SupBody as nvarchar(max)
Declare @EmpEmailSubject as nvarchar(100)
Declare @SupEmailSubject as nvarchar(100)
Declare @HREmailSubject as nvarchar(100)
Declare @EvalDueDate as nvarchar(100)
Declare @LoopCounter as int
Declare @RecordCount as int

set nocount on
create table #EvalsDueInOneMonth (
firstname nvarchar(100),
lastname nvarchar(100),
midname nvarchar(100),
eval_month int,
eval_day int,
email nvarchar(100),
supervisor int,
KeepOffEvalReport bit,
position int,
super_alt int,
superemail nvarchar(100),
eval_compl_on datetime,
nextevalduedate datetime
)
INSERT INTO #EvalsDueInOneMonth exec sp_GetEvalsDueIn1Month
select @RecordCount = count(position) from #EvalsDueInOneMonth

declare cur cursor
for
select firstname, lastname, email, superemail, convert(char(10), nextevalduedate, 110)
from #EvalsDueInOneMonth

open cur
set @LoopCounter = 0
/* Start loop here */
while @LoopCounter < @RecordCount
begin
fetch next from cur into @EmpFirstName, @EmpLastName, @EmpEmail, @SupEmail, @EvalDueDate;
set @EmpBody = 'Your next annual evaluation is due in one month, on '+@EvalDueDate+'. Your supervisor has been notified of the due date of your evaluation. If you have any questions, please contact your supervisor or HR Person contact info.'
set @SupBody = 'This is a reminder that '+substring(@EmpFirstName, 1, 1)+substring(lower(@EmpFirstName), 2, 50)+' '+substring(@EmpLastName, 1, 1)+substring(lower(@EmpLastName), 2, 50)+' has an evaluation due in one month on '+@EvalDueDate+'. Please complete the evaluation, obtain signatures and meet with this employee within forty-five days of the due date. If you have any questions, please contact HR Person contact info..'
set @EmpEmailSubject = 'Annual Evaluation Due in 1 Month'
set @SupEmailSubject = 'Annual Evaluation Due in 1 Month'
set @HREmailSubject = @EmpFirstName+' '+@EmpLastName+' '+'Annual Evaluation Due in 1 Month'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'HR_Eval_Notices',
@recipients = @EmpEmail,
@body = @EmpBody,
@subject = @EmpEmailSubject ;

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'HR_Eval_Notices',
@recipients = @SupEmail,
@body = @SupBody,
@subject = @SupEmailSubject ;

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'HR_Eval_Notices',
@recipients = 'HR1@Person.email',
@body = @EmpBody,
@subject = @HREmailSubject ;

/* Email Sending Block for HR2 and HR3, comment out when Beta testing is done

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'HR_Eval_Notices',
@recipients = 'HR2@Person.email',
@body = @EmpBody,
@subject = @EmpEmailSubject ;

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'HR_Eval_Notices',
@recipients = 'HR2@Person.email',
@body = @SupBody,
@subject = @SupEmailSubject ;

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'HR_Eval_Notices',
@recipients = 'HR3@Person.email',
@body = @EmpBody,
@subject = @EmpEmailSubject ;

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'HR_Eval_Notices',
@recipients = 'HR3@Person.email',
@body = @SupBody,
@subject = @SupEmailSubject ;*/

SET @LoopCounter = @LoopCounter + 1;
/* End loop here */
end
close cur
deallocate cur
drop table #EvalsDueInOneMonth

END

 

[sparkbyte]

This might be a silly question but, are any of the recipients groups? Could be she is a member of a group (or two) that is causing the duplicate emails.

Thanks

John Fuhrman

 

[SiobhanP]

Nope, no groups. It's all individual email addresses.

 

[sparkbyte]

How is the SP being ran? Could it be that it is actually being ran three times somehow.

Thanks

John Fuhrman

 

[SiobhanP]

I have a job that calls it once a day. It's not being run three times because no one else is getting three emails.

I put her in for a CC on the employee copy, and she gets only one, and is happy with that.

I still wish I knew what the heck was going on with this. I've pored over it so many times and I can't see a single thing that should be sending -just her- three copies. Or maybe... maybe it wasn't three copies and she only thought it was because it was three employees that day, and she just reported it to me incorrectly (I did ASK her specifically that question, but she still could have misunderstood I suppose).

Well, at this point, I guess we can consider this done. If it crops up again, I'll look further into it. Probably not worth anyone else's time.

 

[SimonSellick]

Nothing leaps out. I would ask to see the three messages to check that they are really duplicates. I don't suppose that you have three employees with the same name who joined on the same day? Or (more likely) some test data hanging around in the production system? Or someone forgot to use dummy email addresses in the test system? Or...no, I'm not going to tell you ALL the stupid things I've done in the past!