April 17, 2012 at 11:27 am
Hi folks! I have a slew of SSIS packages/SQL Agent jobs that e-mail reports to recipients within my company as well as external recipients. I set up my SMTP Connection Manager to use my company's SMTP server and everything works just fine... until someone leaves the company. Apparently, the SMTP server validates internal company e-mails and makes sure that they exist; it could care less about the external addresses. When someone leaves the company, the Send Mail Task generates a failure message (and fails the SQL Agent job), however, the e-mail still goes out to the rest of the recipients.
Does anyone know how to stop the process from validating internal company addresses? Basically, I just want the error messages to go away so the SQL Agent job treats this as a success. Poorly formatted addresses are a whole other issue that I've already addressed so, I'm not concerned about that at all. I suppose, another way to go about this is to see if there's some way to validate addresses prior to sending. The problem with this workaround is that it means I'd have to spam everyone with test e-mails. Is there a way to validate addresses without actually sending them an e-mail?
________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
April 17, 2012 at 12:45 pm
If I'm not mistaken, the best way to validate the addresses before sending them would be to query the AD data. Here's an article about doing that: http://www.databasejournal.com/features/mssql/article.php/3849891/Query-Active-Directory-Data-from-SQL-Server-using-T-SQL.htm
What I prefer to do is set up Exchange e-mail groups and send reports and such to those. That way, personnel changes don't impact the process. If Report A goes to Joe in Marketing, and Joe is replaced by Jim, then you have to update the code to use the new e-mail address. If Report A goes to "MarketingGroup" in Exchange, and Joe is removed from MarketingGroup and Jim is added, by the people who administer AD and Exchange, then you don't have to change a single thing code-wise, configuration-wise, or otherwise, and the report just keeps going out, and gets to the right people.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply