1

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?

1

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 ?

  • 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 SERVICE\SQLSERVERAGENT? Because that account has exactly the same rights as mine -sysadmin. – BeanFrog Oct 21 '16 at 12:23
0

Has been a while, but in which context was the job (step) executed? When you execute the script in SSMS you are executing in the context of your login, which does have sufficient privileges.

SQL Server Agent Service Permissions

If the job is executed in the context of the SQL Server Agent Service account, then the statement might not have been executed due to missing permissions.

The SQL Server Agent Service uses the NT SERVICE\SQLSERVERAGENT account, only if your service has been set up to run with the default account during setup.

If you are using a dedicated <domain>/<user> account, then that account has to have a corresponding SQL Server Login in your SQL Server instance.

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.