Database Mail
-
I'm using the database mail aspect of SQL 2008 successfully. By this I mean that it sends me out the results I'm wanting, albeit it's sending it out to me twice and I can't find anywhere that I've put the thing in twice.
My problem though is that when I run it through SSMS I can send just find to 3rd party emails, etc. the emails aren't going (I can send internally through my Exchange server). However, when I log into my Exchange server and automatically create an email, send it to my personal Gmail (the account I'm testing with), etc I have no problem whatsoever. It seems to be limited for me when I try to use my SQL server.
(Note, I'm using my own personal Exchange email account to send the emails, so I have complete control over things).
Thus, I have two questions...anyone might be able to shed light on why everytime I send an email using this I end up getting it twice (typically one minute apart), and why it won't send out to a 3rd party email provider.
My code snippet is below :
declare @ProcessDate datetime
set @ProcessDate=(Select CONVERT(varchar(10),getdate(),101))
declare @Result varchar(500)
declare @Title varchar(250)
set @Result='set Nocount on select sourcetable,COUNT(*) as LeadsImported from tbl1 d with(nolock) where createdatetime>=''' + convert(varchar(10),@ProcessDate,101) + ''' group by sourcetable'
set @Title='Results for Import Date : ' + CONVERT(varchar(10),@ProcessDate,101)
EXEC msdb.dbo.sp_send_dbmail @profile_name='Database Mail',
@recipients='blahblahblah@gmail.com',
@subject=@Title,
@query = @Result,
@attach_query_result_as_file = 0
-
Terry this is a pretty common situation, and the issue has to do with relaying and your exchange server / mail server.
mail servers are set up to avoid spam. as a result, if someone connects via SMTP anonymously, a mail server will accept emails for the domains it manages, and return an error 5.7.1 <no Relaying allowed> error back for emails outside of those domains.
to resolve this, you can do one of two things:
1. on the mail server, set it up to accept/allow anonymous emails from the IP address of the SQL server that is sending the emails.
2. change your profile in SQL server to send basic authentication and pass the username and password of an account.
mail servers WILL relay for an account that is fully authenticated.
either of those steps will resolve your issue.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks for the reply, however I'd actually already set that up, for that very reason.
Just not sending externally, and the other issue is the fact that it sends twice, everytime.
-
not sending externally is definitely a relaying issue, that was my point....it's not really a SQL issue anymore if you know you've already added everything for basic authentication. get with your network admin on fixing the relaying issue.
the sending twice part, i'm not sure about.
Terry Gamble (7/7/2010)
Thanks for the reply, however I'd actually already set that up, for that very reason.Just not sending externally, and the other issue is the fact that it sends twice, everytime.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply