• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 787
  • Last Modified:

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;

Open in new window

0
patd1
Asked:
patd1
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what error do you get?
0
 
patd1Author Commented:
No.error. I get command executed successfully, but I don't get the email.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
then, as the query is different, maybe the query results is so big that the email is blocked by the email server?
you should check with the email server admins to see if they see the email in some queue...
0
 
Anthony PerkinsCommented:
No.error. I get command executed successfully, but I don't get the email.
If it was successful you should have seen "Mail Queued".  So you need to find out first why it was not placed on the queue.
0
 
patd1Author Commented:
I modified the query to select top 100, an ran it for testing. it worked.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now