1

I am using Database Mail to send mail from a SQL Server 2014 and sending email to my gmail account for testing purpose from production server.I am getting test email but while executing below sp by SQL job I am getting error:

The mail could not be sent to the recipients because of the mail server failure. The SMTP server requires a secure connection or the client was not authenticated.

DECLARE @query VARCHAR(1000)
DECLARE @msg VARCHAR(250)
declare @subject varchar(100)
DECLARE @query_attachment_filename NVARCHAR(520)
SELECT @msg = 'Please refer to the attached  sheet for the report.'
 select @query='Exec Test.dbo.sp_resultset'
EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'DB_Test',
        @recipients = 'companydba@gmail.com',
         @subject = 'queryresultset',
              @copy_recipients = 'companydba@gmail.com',
       @body = @msg,
          @query=@query,                    
              @attach_query_result_as_file = 1,
              @query_attachment_filename = 'result.csv',
                 @query_result_separator = '   ' ,
                       @query_result_no_padding =1;

Thanks in advance.

  • It failed because your profile is setup and not using a secure connection to the mail server... docs.microsoft.com/en-us/sql/relational-databases/database-mail/… note the secure connection parameter. – Sean Gallardy Mar 29 '17 at 18:17
  • Have you been able to send a regular test email from that server? The error makes it appear as though your SMTP server is using authentication, and you do not have authentication configured (or that you are not using the correct port). – Nic Mar 29 '17 at 18:17
  • I sent test mail by production server but while executing sp error is coming – somu Mar 29 '17 at 18:23
  • Your profile name is DB_Test but what profile did you use to send the test email? – Sean Gallardy Mar 29 '17 at 21:03
  • Issue was with Profile name.I was giving wrong profile name so when I changed to existing profile name and It works. Thanks for help – somu Mar 30 '17 at 13:55
0

I think that Sean Gallardy is right. When you configured your mail profile in the configuration wizard, you set it for non-authenticate and the email server expects authentication (it might be the other way around). I do agree with your statement that it should not work Somu, but I wish to point out that sometimes this happens with systems. I call it wonky behavior.

The answer about the test email is not clear. If you right click the email in SSMS and send a test email, it works fine?

  • yes.if i am sending test email i am getting it. – somu Mar 30 '17 at 1:37
0

Can you try this? When setting the variables can you change SELECT TO SET and change your query?

 SET @msg = 'Please refer to the attached  sheet for the report.'
 SET @query='SELECT GETDATE();'
  • If the above works, then I would troubleshoot the stored procedure. – Jason Mar 29 '17 at 21:02
  • stored Procedure is correct as it is returning result set – somu Mar 30 '17 at 1:45
  • Try setting the @execute_query_database parameter – Jason Mar 30 '17 at 12:47
  • Actually, after looking at your code again; that probably will not help. If you substitute the stored procedure with 'SELECT GETDATE()', does that execute properly? – Jason Mar 30 '17 at 13:07
  • Query is executing but result os not coming to email,Error message:The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2017-03-30T12:29:42). Exception Message: Cannot send mails to mail server. (Transaction failed. The server response was: 5.7.1.Access denied – somu Mar 30 '17 at 13:26
0

Issue was with Profile name.I was giving wrong profile name so when I changed to existing profile name and It works. Thanks for help

  • You should mark this as the accepted answer. – James Jenkins Apr 22 at 16:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.