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.
- ErrorReceivers
- 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.
