January 21, 2014 at 1:52 pm
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;
Forum User:cool:
January 22, 2014 at 9:29 am
Does the proc fail with error or does the proc succeed but the email does not get sent?
If proc fails, provide error. If email does not get sent, check dmail log.
SELECT
*
FROM
msdb.dbo.sysmail_event_log er
where event_type = 'error'
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply