— By Lori Brown @SQLSupahStah
While Try-Catch has been around since SQL 2005, you would be surprised at how infrequently applications and SQL programmers use this to handle errors. Since I also write my own maintenance and monitoring code and jobs that has to be deployed to my clients, I started making an effort to handle errors especially in implementation scripts and in the jobs that I create.
Here is a simple example of a Try-Catch block:
[Some SQL Statement]
[Error Handling or some other SQL Statement]
Basically the Try block is placed around the statement that you want to capture error information on and the Catch block is placed around the statement that defines what you want to happen if there is an error. Pretty simple! 🙂
There are some rules….A Try block must be immediately followed by a Catch block and only catches errors that have severity 10 or higher so it will catch a lot of things. You can use them in a ton of places (triggers, jobs, stored procedures to name a few) and you can nest Try-Catch blocks but make sure you know where the block will exit to if it encounters an error. It won’t catch errors with severity 20 or above, compile errors, or anything that breaks or disconnects a connection (like a KILL statement).
There are handy system functions that can be used to get information on the error while in the Catch block.
I use a Try-Catch block in a job that is used to create a report. Since the report generation is done using a CLR, when it fails it can give some uninformative info as to why which always makes it difficult to troubleshoot. I also wanted to capture run information for my reporting jobs and created a table designed to hold that info along with error messages captured by the Catch blocks should they occur.
— create ReportExecInfo table
CREATE TABLE ReportExecInfo(
ReportName VARCHAR(100) NOT NULL,
Last_Run DATETIME NULL,
Error VARCHAR(1) NULL,
ErrorMessage VARCHAR(100) NULL
) ON [PRIMARY]
CREATE CLUSTERED INDEX [PK_ReportExecInfo] ON [dbo].[ReportExecInfo]
)WITH (FILLFACTOR = 90) ON [PRIMARY]
— Insert report names
INSERT INTO ReportExecInfo (ReportName) VALUES (‘DeadlockReport’)
INSERT INTO ReportExecInfo (ReportName) VALUES (‘BlockingReport’)
INSERT INTO ReportExecInfo (ReportName) VALUES (‘WeeklyHealthReport’)
Then the below code is placed in a job that will log success and failure of the report to generate. I am using the ERROR_MESSAGE() function to grab the error message if one is generated.
DECLARE @rptname VARCHAR(200)
DECLARE @rptpath VARCHAR(200)
DECLARE @attchpath VARCHAR(500)
DECLARE @dtstmp VARCHAR(50)
DECLARE @ServerName VARCHAR(20)
DECLARE @NumRows INT
DECLARE @NumDays INT
DECLARE @cmd VARCHAR(500)
SET @NumDays = 7
SET @rptpath = ‘M:\Reports’
SET @ServerName = @@SERVERNAME
SET @dtstmp = CAST(DATEPART(yyyy, GETDATE())AS VARCHAR(4))+CAST(DATEPART(mm, GETDATE())AS VARCHAR(2))+CAST(DATEPART(dd, GETDATE())AS VARCHAR(2))
SET @rptname = @ServerName+‘-WeeklyHealthReport-‘+@dtstmp
SET @attchpath = @rptpath+‘\’+@rptname+‘.htm’
SET @cmd = ‘EXEC ReportController @Days=’+CAST(@NumDays AS VARCHAR(2))
— Use TRY to catch error if CTE fails
EXEC ADMIN.dbo.RetrieveSQLQueryAndProduceHTMLOutputFile_s @rptpath, @rptname, @cmd, @NumRows
— Capture reason why CTE failed to produce report
UPDATE ReportExecInfo SET Last_Run = GETDATE(), Error = ‘Y’, ErrorMessage = LEFT(ERROR_MESSAGE(), 100) WHERE ReportName = ‘WeeklyHealthReport’
@profile_name = ‘DBMail’,
@recipients = ‘DBA@company.com’,
@subject =‘Weekly Health Report’,
@file_attachments = @attchpath;
— Update ReportExecInfo table with successful run date
UPDATE ReportExecInfo SET Last_Run = GETDATE(), Error = ‘N’ WHERE ReportName = ‘WeeklyHealthReport’
I ran my reports and forced a failure of the CLR on one. Here is what my report log table looks like:
What I am doing is pretty simple but I am sure that you can think of additional ways to use this type of functionality. More info on TRY-CATCH can be found at https://msdn.microsoft.com/en-us/library/ms175976.aspx
For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRX@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!
Please log in again. The login page will open in a new window. After logging in you can close it and return to this page.