Click here to Skip to main content
Click here to Skip to main content

Try Catch in SQL - Reporting stored procedure error in email and also log the error in a table

, 23 Jul 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
Reporting stored procedure error in email and also log the error in a table.

Introduction

Idea: Report an error while executing a stored procedure in email and also log the error in the table.

How to know immediately when there is an error while executing a stored procedure when an application is running or SQL job is running. Just like try and catch statements in application code, we can use try and catch in Stored Procedures and report the error in email and also log it in the table. Four things that we would be interested to log are Stored Procedure Name, Database Name, Error Message and Line number in stored Procedure.

Background

Try and catch in SQL works the same way as it works in Application Code.

Using the code

Step 1: Download the attachment. It has two files.

uspReportStoredProcedureErrorInEmail.txt has a stored procedure.

MailHelperSQLUserDefinedFunctions.txt has few SQL user defined functions.

Step 2: Create the stored procedure, uspReportStoredProcedureErrorInEmail, in the file, uspReportStoredProcedureErrorInEmail.txt in your database. This stored procedure has the code that will report the error in email and also log the error in a table.

In this stored Procedure, please update the profile name in the line(86) mentioned below.

EXEC MSDB.DBO.SP_SEND_DBMAIL @PROFILE_NAME='DBMAIL'

Step 3: Create the functions in the file MailHelperSQLUserDefinedFunctions.txt in msdb database. These functions are used to format the mail which is sent from stored procedure uspReportStoredProcedureErrorInEmail. The stored procedure expects the function to be in msdb database. Idea behind creating the functions in msdb is to use the functions across all the databases in the server.

Step 4: Create a sample stored procedure to know how it works. Sample stored procedure is given below. Please create it in your database. Stored procedure having a line which tries to divide 1 by 0.

Create proc [dbo].[ps_drop] 
as
Begin try
print 1/0
End try
Begin catch
exec uspReportStoredProcedureErrorInEmail
End catch

Step 5: Now execute the Stored Procedure.

Exec [ps_drop]

Step 6: You must see the Message as shown below:

Mail goes to: XYZ@gmail.com
CC in Mail to : XYZ@gmail.com
[DATABASENAME]
Mail queued.

Step 6: So why is the mail sent to XYZ@gmail.com and which table did it log the error?

Executing this stored procedure for the first time creates two tables.

  1. ErrorReceivers
  2. ErrorLogOfStoredProcedures

ErrorReceivers has the mail ids. Mail is sent to two mails with id 1 and 2 in the table.

Errors are logged in the table ErrorLogOfStoredProcedures.

Step 7: Update the mailids in ErrorReceivers Table and execute the stored procedure. You should get the email to the email your updated in the table and also check the log in table.

Exec [ps_drop]
Select * from ErrorLogOfStoredProcedures

Points of Interest

You can update the code to send mails to more than 2 people. Customize the style of mail by updating the functions which provide styles to stored procedure.

History

  • Article created for the first time on 23 July 2013.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

No Biography provided

Comments and Discussions

 
GeneralMy vote of 4 PinprofessionalSibeesh KV24-Sep-14 19:50 
Suggestionanother wonder from MS PinmemberArash M. Dehghani23-Jul-13 11:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141223.1 | Last Updated 23 Jul 2013
Article Copyright 2013 by Smitten Ediot
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid