Hi,
I am using smtp_to() email function inside the catch block to send failure email. The following code I am using:
smtp_to(‘emailID@XX.com’, 'Error: [Job_Name()] FAILED on [host_name()] ', ‘The [job_name()] FAILED on [host_name()]’, 10, 10);
But the failure email is not working. For the success email notification(out side the catch block), I am getting email with out any issues.
Please help me to figure out why the failure smtp_to() function is not working in catch block.
Hi,
Dont Mind, there is no way that it doesnt work. Did you raise the error giving eception such that it will be caught in catch block for that to run smtp_to() in that block?
Yes, In the exception part I selected Catch All Exceptions. I simulated a failure scenario like, in the Query transformation I assigned a wrong filter and the job got failed. But I didn’t get failure email for this scenario.
I created another scenario like primary key violation, for this scenario failure email function is working. Please some one let me know about this.
Manivannan, did you ever resolve this issue? Most of my try/catch code is written such that I use the try/catch to make sure the error doesn’t get past me. I set a variable to indicate if the code in the try section worked ( 0 ) or failed (-1). Outside the try/catch I check the variable and if it is -1 then I use smtp_to and send an email notification and the raise an exception.
It’s been a while since I posted that. Here is what I’m thinking that I was referring to:
# Set the job as completed
try
begin
$ETL_LOAD_LOG_KEY_DECIMAL = $ETL_LOAD_LOG_KEY;
CDW.CDW.PKG_CDW_UDH.UPDATE_ETL_LOAD_LOG_ROW($ETL_LOAD_LOG_KEY_DECIMAL, $RowsInsertedQty, $RowsUpdatedQty, $RowsDeletedQty, $JobStatusCompleted);
end
catch ( all )
begin
$ETL_LOAD_LOG_KEY_DECIMAL = -1;
end
# Check to see if the row was updated
if ($ETL_LOAD_LOG_KEY_DECIMAL = -1)
begin
# The row was not updated so send an email and then kill the job
$JobFailureMessage = 'The job [job_name()] could not update the job status row in CDW.ETL_JOB_LOG for table [$TableName] for target system [$TargetSystem]. The job cannot continue.';
raise_exception_ext($JobFailureMessage, Get_Job_Failure_Code());
end
print('-- Load of table [$TableName] for target system [$TargetSystem] completed.');
Oh ok, now I see what you’re saying…
You catch the exception, simply set the variable and come out of the catch block(s)!
Then you initialize the body of the email, as needed, and raise exception again!!!
(BOB member since 2006-03-15)
(BOB member since 2007-09-12)