April 18, 2012 at 9:37 am
I have a job that emails out shipment notifications at the end of the day to our customers. The problem I have is I don't understand why the same email is sending out twice within a minute of each other when the job is only scheduled to run once. If I take the code out of the step and run it in management studio it only emails once. I attached the code for one customer for reference. We are running SQL 2008 on a VM sending to an exchange 2010 server. Thanks for your help.
Jason
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =N'<H1>XYZ Company ASN For ' + CONVERT(VARCHAR(10), GETDATE(), 101) + ' </H1>' +
N'<table border="1">' +
N'<tr><th>Vendor</th><th>Delivery Date</th>' +
N'<th>Purchase Order Number</th><th>Item Number</th><th>Item Description</th>' +
N'<th>Quantity Shipped</th><th>UOM</th><th>BOL Number</th>' +
N'<th>Transit Days</th></tr>' +
CAST ( ( SELECT td = 'CL&D', '', 'right' AS "td/@align", td = CASE WHEN Datepart(Dw, getdate()) + isnull(tblZipCodes.TransitDays,5) > 6 THEN CONVERT(VARCHAR(12), Shipments.ShipDate + isnull(TransitDays,5) + 2, 101)
ELSE CONVERT(VARCHAR(12), Shipments.ShipDate + isnull(TransitDays,5) + 2, 101) END, '', 'right' AS "td/@align",
td = OpenJob.CustomerOrderN, '', td = OpenJob.CustomerPartN, '', td = OpenJob.JobDescription, '', 'right' AS "td/@align",
td = Ocon.dbo.fn_AddCommasToNumberString(STR(isnull(ShipmentItems.Quantity ,0),12,0)), '', 'right' AS "td/@align", td = ShipmentItems.QuantityCarton, '', 'right' AS "td/@align", td = ShipmentBOL.BOLNumber, '', 'right' AS "td/@align", td = tblZipCodes.TransitDays
FROM (Select *, 4 as division from sed.dbo.Shipments union all Select *, 1 division from Ocon.dbo.Shipments) AS Shipments INNER JOIN
(Select *, 4 as division from sed.dbo.ShipmentBOL union all Select *, 1 division from Ocon.dbo.ShipmentBOL) AS ShipmentBOL ON
Shipments.ShipmentNumber = ShipmentBOL.ShipmentNumber and Shipments.division = ShipmentBOL.division INNER JOIN
(Select *, 4 as division from sed.dbo.ShipmentItems union all Select *, 1 division from Ocon.dbo.ShipmentItems) AS ShipmentItems ON
Shipments.ShipmentNumber = ShipmentItems.ShipmentNumber and Shipments.division = ShipmentItems.division INNER JOIN
(Select *, 4 as division from sed.dbo.OpenJob union all Select *, 1 division from Ocon.dbo.OpenJob) AS OpenJob ON
ShipmentItems.MainReference = OpenJob.JobN and ShipmentItems.division = OpenJob.division LEFT OUTER JOIN
(Select *, 4 as division from Logic_SED.dbo.tblZipCodes union all Select *, 1 division from Logic_Ocon.dbo.tblZipCodes) as tblZipCodes ON
Shipments.PostalCode = tblZipCodes.Zip and Shipments.division = tblZipCodes.division
WHERE (OpenJob.CustomerN = 872) AND (Shipments.ShipDate >= CAST(FLOOR(CAST(GETDATE() AS Float)) AS Datetime)) FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'IT Department',
@recipients = 'user@xyzcompany.com',
@subject = 'CL&D ASN For XYZ',
@body = @tableHTML,
@body_format= 'HTML';
April 18, 2012 at 10:04 am
Have you verified that the email is getting "sent" twice, or cound it be that it is getting sent once and 2 emails are being received?
Jared
CE - Microsoft
April 18, 2012 at 10:06 am
I don't quite understand what you are asking. Can you give me some insight in how I could look into that? I get copied on all the emails that are sent so I have them if there is something I need to look into there.
Jason
April 18, 2012 at 10:15 am
Run this:
SELECT *
FROM msdb.dbo.sysmail_allitems
WHERE subject = 'your subject'
ORDER BY sent_date DESC;
Look at the records and verify that SQL Server is itself sending 2 emails.
Jared
CE - Microsoft
April 18, 2012 at 10:19 am
I've never had it where SQL would send two emails. This is all driven by database tables and an executable. I would er towards checking my email client for a rule which is duplicating the email into a folder.
April 18, 2012 at 10:20 am
Check this table for the duplications
SELECT *
FROM [msdb].[dbo].[sysmail_mailitems]
April 18, 2012 at 10:52 am
There are multiple records listed in this table for the same message which have corresponding sent times that match the emails I received. The send_request_date is different for the 2 emails being off by a 1 min 6 seconds.
Jason
April 18, 2012 at 12:21 pm
Does the job have a retry or multiple schedules?
Can you script out and supply the job?
April 18, 2012 at 1:35 pm
I did some additional testing and it seems like it only sends twice when there is an external email address listed. I then checked the mail log and see this following error message showing up. Any idea what this means?
"The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2012-04-18T14:30:57). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay)."
So basically if there are only internal emails then it sends once and works fine. If there are outside email addresses then it sends twice internally and nothing goes to the outside email.
Jason
April 2, 2015 at 12:26 am
hi
I am facing this issue,
if there are only internal emails then it sends once and works fine. If there are outside email addresses then it sends twice internally and nothing goes to the outside email.
How can i overcome this.Please help me.
Thanks in advance!
April 2, 2015 at 3:13 am
we got the solution issue was with smtp server
December 31, 2015 at 3:56 am
I then checked the mail log and see this following error message showing up.
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2015-12-31T15:28:03). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.1.1 sorry, no mailbox here by that name (chkuser)).
)
Basically if there is multiple email id and any one of them is invalid email id then it sends twice. Having checked the log found that we have retry attempt for failed mail item hence the mail is triggered twice.
January 20, 2016 at 3:29 pm
rakesh.naik (12/31/2015)
I then checked the mail log and see this following error message showing up.The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2015-12-31T15:28:03). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.1.1 sorry, no mailbox here by that name (chkuser)).
)
Basically if there is multiple email id and any one of them is invalid email id then it sends twice. Having checked the log found that we have retry attempt for failed mail item hence the mail is triggered twice.
So is there a configuration somewhere in the SMTP server (sorry I do not know too much about this) that has the retry apply only to the addresses that failed? I assume the retry attempt is by the smtp for the job successfully delivered the call to the smtp server.
----------------------------------------------------
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply