SQL database mail sending test mail not query result
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
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.
sql-server database-mail
asked Mar 29 '17 at 18:07
somusomu
858
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
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.
sql-server database-mail
asked Mar 29 '17 at 18:07
somusomu
858
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
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
add a comment |
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.
sql-server database-mail
asked Mar 29 '17 at 18:07
somusomu
858
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.
sql-server database-mail
sql-server database-mail
asked Mar 29 '17 at 18:07
somusomu
858
asked Mar 29 '17 at 18:07
somusomu
858
asked Mar 29 '17 at 18:07
somusomu
858
asked Mar 29 '17 at 18:07
somusomu
858
asked Mar 29 '17 at 18:07
somusomu
858
858
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
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
add a comment |
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
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
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
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
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
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
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
add a comment |
3 Answers
3
active
oldest
votes
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?
answered Mar 29 '17 at 20:51
Duane LawrenceDuane Lawrence
451211
yes.if i am sending test email i am getting it.
– somu
Mar 30 '17 at 1:37
add a comment |
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();'
answered Mar 29 '17 at 20:57
JasonJason
3061313
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
|
show 2 more comments
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
answered Mar 30 '17 at 13:55
somusomu
858
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f168549%2fsql-database-mail-sending-test-mail-not-query-result%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
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?
answered Mar 29 '17 at 20:51
Duane LawrenceDuane Lawrence
451211
yes.if i am sending test email i am getting it.
– somu
Mar 30 '17 at 1:37
add a comment |
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?
answered Mar 29 '17 at 20:51
Duane LawrenceDuane Lawrence
451211
yes.if i am sending test email i am getting it.
– somu
Mar 30 '17 at 1:37
add a comment |
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?
answered Mar 29 '17 at 20:51
Duane LawrenceDuane Lawrence
451211
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?
answered Mar 29 '17 at 20:51
Duane LawrenceDuane Lawrence
451211
answered Mar 29 '17 at 20:51
Duane LawrenceDuane Lawrence
451211
answered Mar 29 '17 at 20:51
Duane LawrenceDuane Lawrence
451211
answered Mar 29 '17 at 20:51
Duane LawrenceDuane Lawrence
451211
451211
yes.if i am sending test email i am getting it.
– somu
Mar 30 '17 at 1:37
add a comment |
yes.if i am sending test email i am getting it.
– somu
Mar 30 '17 at 1:37
yes.if i am sending test email i am getting it.
– somu
Mar 30 '17 at 1:37
yes.if i am sending test email i am getting it.
– somu
Mar 30 '17 at 1:37
add a comment |
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();'
answered Mar 29 '17 at 20:57
JasonJason
3061313
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
|
show 2 more comments
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();'
answered Mar 29 '17 at 20:57
JasonJason
3061313
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
|
show 2 more comments
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();'
answered Mar 29 '17 at 20:57
JasonJason
3061313
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();'
answered Mar 29 '17 at 20:57
JasonJason
3061313
answered Mar 29 '17 at 20:57
JasonJason
3061313
answered Mar 29 '17 at 20:57
JasonJason
3061313
answered Mar 29 '17 at 20:57
JasonJason
3061313
3061313
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
|
show 2 more comments
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
If the above works, then I would troubleshoot the stored procedure.
– Jason
Mar 29 '17 at 21:02
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
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
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
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
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
|
show 2 more comments
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
answered Mar 30 '17 at 13:55
somusomu
858
add a comment |
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
answered Mar 30 '17 at 13:55
somusomu
858
add a comment |
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
answered Mar 30 '17 at 13:55
somusomu
858
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
answered Mar 30 '17 at 13:55
somusomu
858
answered Mar 30 '17 at 13:55
somusomu
858
answered Mar 30 '17 at 13:55
somusomu
858
answered Mar 30 '17 at 13:55
somusomu
858
858
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f168549%2fsql-database-mail-sending-test-mail-not-query-result%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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