msdb.dbo.sp_send_dbmail does not work with attachment

Using sql server 2012.

I am using the following code to send query results as attachment to an email. It works in one of my sproc and does not work in the other. The only difference is the query, the attachment file name, subject and body message. bothe are executed under the same user.

Please help me understand why this problem, or suggest alternate solution. This user does not have permission to use xp_cmdshell, and is not sysadmin.

Thank You.

DECLARE @query NVARCHAR(MAX)
	DECLARE @DatePostFix varchar(8) =  CONVERT(VARCHAR(8), GETDATE(), 112)
	Declare @AttachmentFileName varchar(300) = 'Eligible_Customers_'+@DatePostFix+'.csv'
	DECLARE @query_result_separator CHAR(1) = char(9); --comma
	SET @query = 'set nocount on;select * from EligibleCustomers order by survey_designator desc; set nocount off;';
	EXEC msdb.dbo.sp_send_dbmail
	  @profile_name     = 'MyDBAutomatedMailer',
      @recipients       = 'myname@mycompany.org',                                               
      @subject          = 'Monthly Eligible Patients for Press Ganey Rpt.',
      @body             = 'Attached is your Monthly Eligible Customers Rpt.',
      @body_format      = 'HTML',
      @importance       = 'High',
      @query_attachment_filename = @AttachmentFileName,
      @attach_query_result_as_file = 1, 
      @query_result_header = 1, -- include column headers
      @exclude_query_output = 1, -- still seems to include query output (number of rows) & so using set nocount on
      @query_result_width = 1000,  
      @append_query_error = 1,
      @query_result_no_padding = 1, -- do not pad columns
      @query_result_separator = @query_result_separator,
      @query = @query;

Select allOpen in new window