March 8, 2019 at 7:03 am
Hello everyone.
Let me first explain what the job does. Basically it executes a T-SQL query (customer data) puts the data in an excel file then send it by email.
This job works perfectly when I run it manually, but once I schedule it, I receive nothing.
When I look at the logs, I see that the job has been executed successfully. I don't understand why the email never arrives.
Below the code of the request. Thank you so much for your help 🙂
DECLARE @msg VARCHAR(250);
DECLARE @query varchar(max);
SET @msg = 'Please refer to the attached CSV for the Cust report.';
SET @query='
print ''sep=;''
select
mmsmbrmbcd ''MemberCode'',
mmsmbrsnmp ''Name'',
mmsmbrref2 ''SMS Optoin'',
mmsmbrageg ''Age group'',
mmsmbralph ''Phone option'',
mmsmbralma ''Mail (letter) option)'',
mmsmbrlang ''Language''
from mmsmbrmst
where mmsmbrcpcd = ''A142''
order by mmsmbrcrdt ASC '
;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'noreply@pcsi.com'
, @recipients = 'ext@pcsi.com'
, @subject = 'PL CUST Reprot'
, @body= @msg
, @body_format = 'TEXT'
, @query = @query
, @execute_query_database = 'PLPCD'
,@query_result_separator = ';'
, @attach_query_result_as_file = 1
, @query_result_width = 8000
,@exclude_query_output =1
,@query_result_header =1
, @query_attachment_filename='Report_created_members_per_month.csv'
,@query_result_no_padding=1
March 11, 2019 at 4:58 pm
One thing to check is whether the login used for the SQL Agent has permissions to see the table in question, and to use the sp_send_dbmail procedure, and to create the necessary report. One way to find out is to log in to SSMS using the same credentials SQL Agent uses, then try to run the same SQL statements. If it produces errors, you should be able to see them there.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply