send_db_mail with attachment query can't see user databases
I have a SQL Agent job that calls send_db_mail with the following code:
DECLARE @FileName VARCHAR(30)
SET @FileName= 'FileName'+CONVERT(VARCHAR(20),GETDATE(),112)+'.csv'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL Admins',
@recipients = 'my@email.address.com',
@body = 'Attached are the results of the query.',
@query = 'SELECT columns from MyDatabase.dbo.MyTable',
@subject = 'Test Run',
@attach_query_result_as_file = 1 ,
@query_attachment_filename = @FileName,
@query_result_separator = ',',
@query_result_no_padding=1,
@query_result_header = 0,
@query_result_width = 32767,
@exclude_query_output=1,
@append_query_error = 1;
When I run that code through SSMS, I get an email, and it works fine. When I run the job, it succeeds, but I receive no email.
I have checked the following:
- Nothing appears in
sysmail_allitems
- No errors appear in
sysmail_event_log
- If I change
@query = 'select 1 as squirrel'it will send an email from the job. - If I change
@query = 'select name from master.sys.databases'it will send an email from the job.
There is no error message given when no email is sent, and the job even lists as succeeded.
I'm not sure where to look next - can anyone suggest where to look for problems?
sql-server sql-server-2014 database-mail
asked Oct 21 '16 at 11:40
BeanFrogBeanFrog
1437
bumped to the homepage by Community♦ 5 hours 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 have a SQL Agent job that calls send_db_mail with the following code:
DECLARE @FileName VARCHAR(30)
SET @FileName= 'FileName'+CONVERT(VARCHAR(20),GETDATE(),112)+'.csv'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL Admins',
@recipients = 'my@email.address.com',
@body = 'Attached are the results of the query.',
@query = 'SELECT columns from MyDatabase.dbo.MyTable',
@subject = 'Test Run',
@attach_query_result_as_file = 1 ,
@query_attachment_filename = @FileName,
@query_result_separator = ',',
@query_result_no_padding=1,
@query_result_header = 0,
@query_result_width = 32767,
@exclude_query_output=1,
@append_query_error = 1;
When I run that code through SSMS, I get an email, and it works fine. When I run the job, it succeeds, but I receive no email.
I have checked the following:
- Nothing appears in
sysmail_allitems
- No errors appear in
sysmail_event_log
- If I change
@query = 'select 1 as squirrel'it will send an email from the job. - If I change
@query = 'select name from master.sys.databases'it will send an email from the job.
There is no error message given when no email is sent, and the job even lists as succeeded.
I'm not sure where to look next - can anyone suggest where to look for problems?
sql-server sql-server-2014 database-mail
asked Oct 21 '16 at 11:40
BeanFrogBeanFrog
1437
bumped to the homepage by Community♦ 5 hours 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 have a SQL Agent job that calls send_db_mail with the following code:
DECLARE @FileName VARCHAR(30)
SET @FileName= 'FileName'+CONVERT(VARCHAR(20),GETDATE(),112)+'.csv'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL Admins',
@recipients = 'my@email.address.com',
@body = 'Attached are the results of the query.',
@query = 'SELECT columns from MyDatabase.dbo.MyTable',
@subject = 'Test Run',
@attach_query_result_as_file = 1 ,
@query_attachment_filename = @FileName,
@query_result_separator = ',',
@query_result_no_padding=1,
@query_result_header = 0,
@query_result_width = 32767,
@exclude_query_output=1,
@append_query_error = 1;
When I run that code through SSMS, I get an email, and it works fine. When I run the job, it succeeds, but I receive no email.
I have checked the following:
- Nothing appears in
sysmail_allitems
- No errors appear in
sysmail_event_log
- If I change
@query = 'select 1 as squirrel'it will send an email from the job. - If I change
@query = 'select name from master.sys.databases'it will send an email from the job.
There is no error message given when no email is sent, and the job even lists as succeeded.
I'm not sure where to look next - can anyone suggest where to look for problems?
sql-server sql-server-2014 database-mail
asked Oct 21 '16 at 11:40
BeanFrogBeanFrog
1437
I have a SQL Agent job that calls send_db_mail with the following code:
DECLARE @FileName VARCHAR(30)
SET @FileName= 'FileName'+CONVERT(VARCHAR(20),GETDATE(),112)+'.csv'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL Admins',
@recipients = 'my@email.address.com',
@body = 'Attached are the results of the query.',
@query = 'SELECT columns from MyDatabase.dbo.MyTable',
@subject = 'Test Run',
@attach_query_result_as_file = 1 ,
@query_attachment_filename = @FileName,
@query_result_separator = ',',
@query_result_no_padding=1,
@query_result_header = 0,
@query_result_width = 32767,
@exclude_query_output=1,
@append_query_error = 1;
When I run that code through SSMS, I get an email, and it works fine. When I run the job, it succeeds, but I receive no email.
I have checked the following:
- Nothing appears in
sysmail_allitems
- No errors appear in
sysmail_event_log
- If I change
@query = 'select 1 as squirrel'it will send an email from the job. - If I change
@query = 'select name from master.sys.databases'it will send an email from the job.
There is no error message given when no email is sent, and the job even lists as succeeded.
I'm not sure where to look next - can anyone suggest where to look for problems?
sql-server sql-server-2014 database-mail
sql-server sql-server-2014 database-mail
asked Oct 21 '16 at 11:40
BeanFrogBeanFrog
1437
asked Oct 21 '16 at 11:40
BeanFrogBeanFrog
1437
asked Oct 21 '16 at 11:40
BeanFrogBeanFrog
1437
asked Oct 21 '16 at 11:40
BeanFrogBeanFrog
1437
asked Oct 21 '16 at 11:40
BeanFrogBeanFrog
1437
1437
bumped to the homepage by Community♦ 5 hours 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♦ 5 hours 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 |
add a comment |
1 Answer
1
active
oldest
votes
When you launch the query through SSMS, it will use your account, but when it is launched through SQLAgent job, it will use the account that runs the service. Can can double check that in send_request_user column of sysmail_allitems table.
Maybe they have different rights ?
answered Oct 21 '16 at 12:07
irimiasirimias
1,619419
It is using the service account, which is a domain account. But that account doesn't seem to have a corresponding login for the server. Does it use the login NT SERVICESQLSERVERAGENT? Because that account has exactly the same rights as mine -sysadmin.
– BeanFrog
Oct 21 '16 at 12:23
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%2f152979%2fsend-db-mail-with-attachment-query-cant-see-user-databases%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
When you launch the query through SSMS, it will use your account, but when it is launched through SQLAgent job, it will use the account that runs the service. Can can double check that in send_request_user column of sysmail_allitems table.
Maybe they have different rights ?
answered Oct 21 '16 at 12:07
irimiasirimias
1,619419
It is using the service account, which is a domain account. But that account doesn't seem to have a corresponding login for the server. Does it use the login NT SERVICESQLSERVERAGENT? Because that account has exactly the same rights as mine -sysadmin.
– BeanFrog
Oct 21 '16 at 12:23
add a comment |
When you launch the query through SSMS, it will use your account, but when it is launched through SQLAgent job, it will use the account that runs the service. Can can double check that in send_request_user column of sysmail_allitems table.
Maybe they have different rights ?
answered Oct 21 '16 at 12:07
irimiasirimias
1,619419
It is using the service account, which is a domain account. But that account doesn't seem to have a corresponding login for the server. Does it use the login NT SERVICESQLSERVERAGENT? Because that account has exactly the same rights as mine -sysadmin.
– BeanFrog
Oct 21 '16 at 12:23
add a comment |
When you launch the query through SSMS, it will use your account, but when it is launched through SQLAgent job, it will use the account that runs the service. Can can double check that in send_request_user column of sysmail_allitems table.
Maybe they have different rights ?
answered Oct 21 '16 at 12:07
irimiasirimias
1,619419
When you launch the query through SSMS, it will use your account, but when it is launched through SQLAgent job, it will use the account that runs the service. Can can double check that in send_request_user column of sysmail_allitems table.
Maybe they have different rights ?
answered Oct 21 '16 at 12:07
irimiasirimias
1,619419
answered Oct 21 '16 at 12:07
irimiasirimias
1,619419
answered Oct 21 '16 at 12:07
irimiasirimias
1,619419
answered Oct 21 '16 at 12:07
irimiasirimias
1,619419
1,619419
It is using the service account, which is a domain account. But that account doesn't seem to have a corresponding login for the server. Does it use the login NT SERVICESQLSERVERAGENT? Because that account has exactly the same rights as mine -sysadmin.
– BeanFrog
Oct 21 '16 at 12:23
add a comment |
It is using the service account, which is a domain account. But that account doesn't seem to have a corresponding login for the server. Does it use the login NT SERVICESQLSERVERAGENT? Because that account has exactly the same rights as mine -sysadmin.
– BeanFrog
Oct 21 '16 at 12:23
It is using the service account, which is a domain account. But that account doesn't seem to have a corresponding login for the server. Does it use the login NT SERVICESQLSERVERAGENT? Because that account has exactly the same rights as mine -sysadmin.
– BeanFrog
Oct 21 '16 at 12:23
It is using the service account, which is a domain account. But that account doesn't seem to have a corresponding login for the server. Does it use the login NT SERVICESQLSERVERAGENT? Because that account has exactly the same rights as mine -sysadmin.
– BeanFrog
Oct 21 '16 at 12:23
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%2f152979%2fsend-db-mail-with-attachment-query-cant-see-user-databases%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
