SQL agent configured to call a SP

Question

SQL agent configured to call a SP

t2sqldba

SSChasing Mays

Points: 625
More actions
June 9, 2013 at 3:20 am

#273629

All,
My problem scenarion is:
"I have a SQL agent job setup that calls a SP which collects all data and sends a report to some email ids via msdb.dbo.sp_send_dbmail.
The job is scheduled to run daily once around 8:30 PM. Below T-SQL is part of the SP to send the email
exec msdb.dbo.sp_send_dbmail @recipients=@EMAILID,@blind_copy_recipients=@BCC,
@subject = @Subject1 ,
@body = @body1,
@body_format = 'HTML' ;
END"
Users say that they do receive email on the report but they receive 2 similar emails at the same time.
But the job is scheduled to run only once a day and there is no duplicate job to send email for the same report and to same users.
How do I trouble shoot from where the second email is going?
Is @recipients=@EMAILID,@blind_copy_recipients=@BCC the issue?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 1

t2sqldba (6/9/2013)
All,
My problem scenarion is:
"I have a SQL agent job setup that calls a SP which collects all data and sends a report to some email ids via msdb.dbo.sp_send_dbmail.
The job is scheduled to run daily once around 8:30 PM. Below T-SQL is part of the SP to send the email
exec msdb.dbo.sp_send_dbmail @recipients=@EMAILID,@blind_copy_recipients=@BCC,
@subject = @Subject1 ,
@body = @body1,
@body_format = 'HTML' ;
END"
Users say that they do receive email on the report but they receive 2 similar emails at the same time.
But the job is scheduled to run only once a day and there is no duplicate job to send email for the same report and to same users.
How do I trouble shoot from where the second email is going?
Is @recipients=@EMAILID,@blind_copy_recipients=@BCC the issue?

This may be a silly question, but are you sure you didn't leave the same job running in a testing or development environment that could be generating the second e-mail?

I would start by checking the values in your @EMAILID and @BCC variables at run time. I would consider adding a line of code that writes those values to a log table so I could view their values later.

Jason Wolfkill

Calibear Ten Centuries Points: 1377 More actions · Answer 2

Is it possible the stored procedure is sending more than one report? Could you send us a code snippet?

--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455

crmitchell SSCarpal Tunnel Points: 4557 More actions · Answer 3

wolfkillj (6/10/2013)
I would start by checking the values in your @EMAILID and @BCC variables at run time. I would consider adding a line of code that writes those values to a log table so I could view their values later.

Including checking if any of the names are included in any email groups on the lists.

If you are generating the email could also be that it hasn't completed sending and hence hasn't been flagged as sent before the next poll.

Sean Pearce SSCoach Points: 15999 More actions · Answer 4

wolfkillj (6/10/2013)
I would consider adding a line of code that writes those values to a log table so I could view their values later.

SELECT

recipients,

copy_recipients,

blind_copy_recipients,

send_request_date,

sent_date,

sent_status

FROM

msdb..sysmail_allitems

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

vyankarla SSC Enthusiast Points: 159 More actions · Answer 5

found this in one of the post . see below.

1. Open database mail configuration wizard.

2. Select view or change system parameters option

3. set the "Account retry attempts" to 0

t2sqldba SSChasing Mays Points: 625 More actions · Answer 6

Hi All,

Thanks for your replies.

I have used below script

SELECT *

FROM msdb.[dbo].[sysmail_allitems]

And also asked user to share with me the email notification they received.

I could solve the problem.