Database mail on SQ2K5 sending mail twice

Question

Post reply

Database mail on SQ2K5 sending mail twice

simon phoenix-479217

SSCommitted

Points: 1573
More actions
September 25, 2008 at 2:06 pm

#198583

Hi
Fellows
I have a stored procedure that sends the email( including my daily report as body). Suddenly the database mail start sending same mail twice back to back. Does anybody know where the problem is or can point me in the correct direction
Thanks
Simon

Viewing 15 posts - 1 through 15 (of 15 total)

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

Adam Bean One Orange Chip Points: 26631 More actions · Answer 1

Sounds like a problem in the procedure. Feel free to post it here and I'm sure we can be of more help.

simon phoenix-479217 SSCommitted Points: 1573 More actions · Answer 2

Hi

adam

Thanks for the quick response, but i dont think its stored procedure . I think it has to do with Queing of mail from database mail. The stored procedure is simple and the last part of it which sends the email is as

where my report is embeded inside the variable @tableHTML

EXEC MSDB.dbo.SP_SEND_DBMAIL

@profile_name = 'Reporting'

,@recipients = abcd@hotmail.com

, @subject = @Sub

, @query_result_header = 0

,@body = @tableHTML

,@body_format = 'HTML'

Adam Bean One Orange Chip Points: 26631 More actions · Answer 3

Not sure I can buy that Simon ... I've never heard of such a thing and I use database mail very heavily. Not to say it isn't possible ...

Can you post the whole proc as well as how it is called?

Also, check SELECT * FROM msdb.dbo.sysmail_allitems

Ensure that there are in fact two entries every time this process is called. Do they have the exact same time stamp?

simon phoenix-479217 SSCommitted Points: 1573 More actions · Answer 4

Hi

adam

First

it put the value into @tableHTML variable

SET @tableHTML =

N' ' +

N' '+

N' OPI - '+ convert(varchar(20), GetDate() -@Days, 1)

.........................continues pattern

CAST ( ( SELECT td = [LOB], '',

td = [Manager], '',

td = [Top Box], '',

.......................continues pattern

FROM TableA

WHERE ReportDate = @ReportDate

AND (Offered <> '--' OR [Open Cases] <> 0 OR [Closed Cases] <> 0)

ORDER BY OrderSeq

FOR XML PATH('tr'), TYPE

select @tableHTML

EXEC MSDB.dbo.SP_SEND_DBMAIL

@profile_name = ' Reporting'

,@recipients = abcd@hotmail.com

, @subject = @Sub , @query_result_header = 0

,@body = @tableHTML

,@body_format = 'HTML'

Adam Bean One Orange Chip Points: 26631 More actions · Answer 5

Very odd ...

SELECT * FROM msdb.dbo.sysmail_allitems

You see your entries in there twice with the same time stamp?

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 6

I have had this happen outside of SQL. On of my colleagues would send me an email and it would come through twice. We verified that he only sent it once. It happened intermittently and the problem has just gone away, but I have witnessed exchange do this outside of SQL.

I don't think it's likely that you are experience the same thing, but it is certainly possible.

simon phoenix-479217 SSCommitted Points: 1573 More actions · Answer 7

Hi

adam

i see only one time stamp for that particular mail

Thanks

simon

Adam Bean One Orange Chip Points: 26631 More actions · Answer 8

simon phoenix (9/25/2008)
Hi
adam
i see only one time stamp for that particular mail
Thanks
simon

That sounds like an exchange issue then?

To verify, can you just send an email without your logic as a test. Or just use the send test email from SSMS. Also, try on another server ...

simon phoenix-479217 SSCommitted Points: 1573 More actions · Answer 9

Hi

adam

I tried the test mail it worked just fine, just one mail at a time. It seems like exchange problem as you mentioned but do you know forum that i should go in for exchange help or should i start a new thread as exchange issue

Thanks

Simon

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 10

Well, I'm not sure on that one. Like I said, I have experienced that issue outside of SQL, and I'm not really sure what fixed it. I assumed the exchange admins applied a patch since then. It wasn't consistently sending 2 either, just a couple of people's email would double up on me occasionally, but it wasn't consistent, other that it only affected me from a couple of people and it wasn't every email they sent. I didn't want to lead you down the wrong path, just wanted to let you know it may not be a SQL problem. This is going to be a tough one to crack possibly, although you may find it helpful to talk to a good exchange admin. It's still possible that it is a problem on the SQL side, just letting you know I have experienced it outside of SQL.

simon phoenix-479217 SSCommitted Points: 1573 More actions · Answer 11

Thanks

Adam

But, even for me its not every mail that SQL procedure send , its just few of them. Where do i go from here?

Thanks

Simon

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 12

well, it's a real sticky one when dealing with a problem like this where it could be SQL or exchange. it's hard to say where to concentrate your efforts. Personally, I would lean more towards the exchange side. Really, it would be best to eliminate one as the cause so you can concentrate on the side that is causing the problems. Do you have multiple SQL boxes in you environment? If you do, have you seen this behavior with your other SQL Servers?

Adam Bean One Orange Chip Points: 26631 More actions · Answer 13

simon phoenix (9/26/2008)
Hi
adam
I tried the test mail it worked just fine, just one mail at a time. It seems like exchange problem as you mentioned but do you know forum that i should go in for exchange help or should i start a new thread as exchange issue
Thanks
Simon

This really wouldn't be the place to get support on Exchange ... someone may be able to help, but no specific area for it. I'd ask your Exchange admin to investigate.

deep.tkt SSC Veteran Points: 211 More actions · Answer 14

I had the the same issue today and it turned out there was an invalid ID in the mailing list. sysmail_allitems was showing it was triggered once but were getting two emails. After first unsuccessful attempt to invalid id, it was attempting once again after 60secs with whole mailing list.