1. 

   nsepetys, Member

   Hello myDBR Support,

   How are things going? I am trying to do a batch job that will deliver over a hundred emails that each have individual excel attachments. I have noticed some issues where attachments are sporadically not getting generated as desired and are simply empty excel spreadsheets (even though we know there should be results) or we get error messages in the spreadsheet (for example- "contact your admin"). I believe the issue is solely on our end and that our tables get deadlocked when trying to generate the report. We are currently working to resolve this issue in itself, however, is there a way I can check the email attachment before sending it through a myDBR debugging option? Maybe something I can use to check the filesize or preferably parse the attachment contents?

   Thanks,
   Noah
2. 

   myDBR Team, Key Master

   Noah,
   there is couple of things you could do to try to sort the issue out.

   First the "contact your admin" message. A generic error message is shown to user when an error in the database happens. Admins get a full error message with technical details. What you could do is to (temporarily) enable the full error message to all users ($mydbr_defaults['server_error']['show_full_error_message_to_non_admin'] = true;) or (temporarily) run the batch job under admin rights.

   Another thing you can do, is to enable the logging of the mail sending. This will report errors on the attachment level. To do this use option:

   select 'dbr.mail.log.proc', 'sp_mail_log';

   where the sp_mail_log is a procedure with two parameters: msg and the error_text.

   create procedure sp_mail_log(
   
   in_msg varchar(255),
   
   in_error text
   
   )
   
   begin
   
   insert into log....
   
   

   --
   myDBR Team
3. 

   nsepetys, Member

   Thank you for the expedient response. I am seeing lots of "Sent 1 mail with no errors" so far so I believe we're good. Will I get the explicit text that I've seen in the web client if I run across a deadlock when the excel attachment is being processed? Or what message should I be looking out for?

   Also, if I do encounter an error is there a way I can use the logging to tie it to a specific run of my report like by overloading the dbr.mail.log.proc command with a parameter that gets inserted into the log? I suppose I can check the mydbr_statistics table and parse the query column but that would also rely on getting the correct proc call since this proc, that is being attached, is also used in a non-automated fashion by our internal team.

   Additionally, can this logging be used to catch any errors (especially deadlocking issues in the attachment generation) before the emails are sent?
4. 

   myDBR Team, Key Master

   When a deadlock (or other database error) happens, the attachment report run will fail. If this happens in the middle of an Excel file generation, might be that the Excel file will become corrupted. If you open the corrupted Excel file in a text editor, you can look for the error messages within the file.

   The mail log is primarily targeted for the mail level actions (can mail be sent, can attachment be fetched) so it will not help that much with deadlocks.

   if deadlocks are the culprit, you should take a look what is causing deadlocks in the system. Usually there are ways of minimizing the occurrence of deadlocks by optimizing how data is handled by your application.

   --
   myDBR Team
5. 

   nsepetys, Member

   Thank you for your response. Unfortunately, only minimizing the occurrence of deadlocks will not be what we're looking for. We need to eliminate the chance of all possible errors that may happen for these attachments. Without the guarantee or at least some post attachment creation logging that can verify the result of the report proc we simply are not comfortable with automating the emailing of the attachments to our end-users. I understand this is a result of our current framework and not a product of the myDBR tool. We just don't have the time nor the expertise to make this work 100% of the time and guarantee ourselves that we won't have any attachment issues. Have a good weekend.
6. 

   myDBR Team, Key Master

   Does your mail attachment report do updates? If so, you could do the updates separately from the Excel attachments.

   If you update to the latest build, there will be improvements how database errors are handled inside the exports. This includes also deadlock situations. Also the mail log has been extended to include errors for attachments. See documentation for changes.

   --
   myDBR Team

Reply

You must log in to post.