January 23, 2019 at 2:31 pm
Hi,
I have inherited a 1600+ line stored procedure at my work that imports sales orders into our ERP system. The stored procedure is initiated by a scheduled SQL Job. Several times a week the stored procedure errors out during the import of sales orders and does not finish the process. When this happens, generally, the sales order number it errors on doesn't import all order lines. From the logs the error severity is greater than 10 so I know the Try Catch will get the error. I would like to catch the error and then send out an email notification to the appropriate people that an error occurred on this particular order and needs to be reviewed.
My question is what would be the best practice in implementing the try catch in the stored procedure. Basically the stored procedure grabs all orders to be imported inserts the order header and then loops and imports order lines and then goes to the next order. Would it be okay just to encapsulate the whole stored procedure with the try catch or should I break it down in smaller parts such as put a try catch around the order header import then inserted another try catch around the order detail import? From investigation, the majority of the errors are at the order detail line import. Any suggestions on best way to implement or if there is a better way of catching errors and notifying would be much appreciated.
Thanks,
Tim
January 24, 2019 at 7:19 pm
tim.stutzman - Wednesday, January 23, 2019 2:31 PMHi,I have inherited a 1600+ line stored procedure at my work that imports sales orders into our ERP system. The stored procedure is initiated by a scheduled SQL Job. Several times a week the stored procedure errors out during the import of sales orders and does not finish the process. When this happens, generally, the sales order number it errors on doesn't import all order lines. From the logs the error severity is greater than 10 so I know the Try Catch will get the error. I would like to catch the error and then send out an email notification to the appropriate people that an error occurred on this particular order and needs to be reviewed.
My question is what would be the best practice in implementing the try catch in the stored procedure. Basically the stored procedure grabs all orders to be imported inserts the order header and then loops and imports order lines and then goes to the next order. Would it be okay just to encapsulate the whole stored procedure with the try catch or should I break it down in smaller parts such as put a try catch around the order header import then inserted another try catch around the order detail import? From investigation, the majority of the errors are at the order detail line import. Any suggestions on best way to implement or if there is a better way of catching errors and notifying would be much appreciated.
Thanks,
Tim
When this happens to you, does the job list itself as "Failed"?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 25, 2019 at 8:59 am
That is correct.
Tim
January 25, 2019 at 4:20 pm
When the step fails, you could have it execute a step to send you an email that the job failed. I typically don't do that, though.
What I end up doing is as I progress through each section of the code, I set an @Msg variable to identify the section of the code that the proc is currently working on. In the CATCH, I pick up on that variable and send the email from the CATCH code. That not only tells me the job failed but, along with other amplifying information I use in the CATCH email, it gives me a lot more information that what is normally generated for email errors using an email step.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply