Errors in Transact-SQL code can be processed by using a TRY…CATCH construct similar to the exception-handling features of the Microsoft Visual C++ and Microsoft Visual C# languages. A TRY…CATCH construct consists of two parts: a TRY block and a CATCH block. When an error condition is detected in a Transact-SQL statement that is inside a TRY block, control is passed to a CATCH block where the error can be processed.
After the CATCH block handles the exception, control is then transferred to the first Transact-SQL statement that follows the END CATCH statement. If the END CATCH statement is the last statement in a stored procedure or trigger, control is returned to the code that invoked the stored procedure or trigger. Transact-SQL statements in the TRY block following the statement that generates an error will not be executed.
If there are no errors inside the TRY block, control passes to the statement immediately after the associated END CATCH statement. If the END CATCH statement is the last statement in a stored procedure or trigger, control is passed to the statement that invoked the stored procedure or trigger.
A TRY block starts with the BEGIN TRY statement and ends with the END TRY statement. One or more Transact-SQL statements can be specified between the BEGIN TRY and END TRY statements.
A TRY block must be followed immediately by a CATCH block. A CATCH block starts with the BEGIN CATCH statement and ends with the END CATCH statement. In Transact-SQL, each TRY block is associated with only one CATCH block.
When you use the TRY…CATCH construct, consider the following guidelines and suggestions:
TRY block in one batch and the associated CATCH block in another batch. The following script would generate an error:BEGIN TRY
SELECT *
FROM sys.messages
WHERE message_id = 21;
END TRY
GO
-- The previous GO breaks the script into two batches,
-- generating syntax errors. The script runs if this GO
-- is removed.
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
GO
.gif) Note: |
|---|
If a distributed transaction executes within the scope of a TRY block and an error occurs, execution is transferred to the associated CATCH block. The distributed transaction enters an uncommittable state. Execution within the CATCH block may be interrupted by the Microsoft Distributed Transaction Coordinator which manages distributed transactions. When the error occurs, MS DTC asynchronously notifies all servers participating in the distributed transaction, and terminates all tasks involved in the distributed transaction. This notification is sent in the form of an attention, which is not handled by a TRY…CATCH construct, and the batch is ended. When a batch finishes running, the Database Engine rolls back any active uncommittable transactions. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application that indicates an uncommittable transaction was detected and rolled back For more information about distributed transactions, see Distributed Transactions (Database Engine). |
TRY…CATCH uses the following error functions to capture error information:
Error information is retrieved by using these functions from anywhere in the scope of the CATCH block of a TRY…CATCH construct. The error functions will return NULL if called outside the scope of a CATCH block. Error functions can be referenced inside a stored procedure and can be used to retrieve error information when the stored procedure is executed in the CATCH block. By doing this, you do not have to repeat the error handling code in every CATCH block. In the follow code example, the SELECT statement in the TRY block will generate a divide-by-zero error. The error will be handled by the CATCH block, which uses a stored procedure to return error information.
USE AdventureWorks;
GO
-- Verify that the stored procedure does not exist.
IF OBJECT_ID ('usp_GetErrorInfo', 'P') IS NOT NULL
DROP PROCEDURE usp_GetErrorInfo;
GO
-- Create a procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
GO
BEGIN TRY
-- Generate divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
-- Execute the error retrieval routine.
EXECUTE usp_GetErrorInfo;
END CATCH;
GO
There are two types of errors that will not be handled by TRY…CATCH if the error occurs in the same execution level as the TRY…CATCH construct:
When the batch, stored procedure, or trigger that contains the TRY…CATCH construct generates one of these errors, the TRY…CATCH construct does not handle these errors. These errors will return to the application or batch that called the error-generating routine. For example, the following code example shows a SELECT statement that causes a syntax error. If this code is executed in the SQL Server Management Studio Query Editor, execution will not start because the batch fails to compile. The error will be returned to the Query Editor and will not get caught by TRY…CATCH.
USE AdventureWorks;
GO
BEGIN TRY
-- This PRINT statement will not run because the batch
-- does not begin execution.
PRINT N'Starting execution';
-- This SELECT statement contains a syntax error that
-- stops the batch from compiling successfully.
SELECT ** FROM HumanResources.Employee;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
Unlike the syntax error in the previous example, an error that occurs during statement-level recompilation will not prevent the batch from compiling, but it will terminate the batch as soon as recompilation for the statement fails. For example, if a batch has two statements and the second statement references a table that does not exist, deferred name resolution causes the batch to compile successfully and start execution without binding the missing table to the query plan until that statement is recompiled. The batch stops running when it gets to the statement that references the missing table and returns an error. This type of error will not be handled by a TRY…CATCH construct at the same level of execution at which the error occurred. The following example demonstrates this behavior.
USE AdventureWorks;
GO
BEGIN TRY
-- This PRINT statement will run because the error
-- occurs at the SELECT statement.
PRINT N'Starting execution';
-- This SELECT statement will generate an object name
-- resolution error because the table does not exist.
SELECT * FROM NonExistentTable;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
You can use TRY…CATCH to handle errors that occur during compilation or statement-level recompilation by executing the error-generating code in a separate batch within the TRY block. For example, you do this by placing the code in a stored procedure or by executing a dynamic Transact-SQL statement using sp_executesql. This allows TRY…CATCH to catch the error at a higher level of execution than the error occurrence. For example, the following code shows a stored procedure that generates an object name resolution error. The batch that contains the TRY…CATCH construct is executing at a higher level than the stored procedure; and the error, which occurs at a lower level, is caught.
USE AdventureWorks;
GO
-- Verify that the stored procedure does not already exist.
IF OBJECT_ID ('usp_MyError', 'P') IS NOT NULL
DROP PROCEDURE usp_MyError;
GO
CREATE PROCEDURE usp_MyError
AS
-- This SELECT statement will generate
-- an object name resolution error.
SELECT * FROM NonExistentTable;
GO
BEGIN TRY
-- Run the stored procedure.
EXECUTE usp_MyError;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
Here is the result set.
ErrorNumber ErrorMessage
----------- ---------------------------------------
208 Invalid object name 'NonExistentTable'.
For more information, see Deferred Name Resolution and Compilation and the "Recompiling Execution Plans" section in Execution Plan Caching and Reuse.
Inside a TRY…CATCH construct, transactions can enter a state in which the transaction remains open but cannot be committed. The transaction cannot perform any action that would generate a write to the transaction log, such as modifying data or trying to roll back to a savepoint. However, in this state, the locks acquired by the transaction are maintained, and the connection is also kept open. The effects of the transaction are not reversed until a ROLLBACK statement is issued, or until the batch ends and the transaction is automatically rolled back by the Database Engine. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application that indicates an uncommittable transaction was detected and rolled back.
A transaction enters an uncommittable state inside a TRY block when an error occurs that would otherwise have ended the transaction. For example, most errors from a data definition language (DDL) statement (such as CREATE TABLE), or most errors that occur when SET XACT_ABORT is set to ON, terminate the transaction outside a TRY block but make a transaction uncommittable inside a TRY block.
The code in a CATCH block should test for the state of a transaction by using the XACT_STATE function. XACT_STATE returns a -1 if the session has an uncommittable transaction. The CATCH block must not perform any actions that would generate writes to the log if XACT_STATE returns a -1. The following code example generates an error from a DDL statement and uses XACT_STATE to test the state of a transaction in order to take the most appropriate action.
USE AdventureWorks;
GO
-- Verify that the table does not exist.
IF OBJECT_ID (N'my_books', N'U') IS NOT NULL
DROP TABLE my_books;
GO
-- Create table my_books.
CREATE TABLE my_books
(
Isbn int PRIMARY KEY,
Title NVARCHAR(100)
);
GO
BEGIN TRY
BEGIN TRANSACTION;
-- This statement will generate an error because the
-- column author does not exist in the table.
ALTER TABLE my_books
DROP COLUMN author;
-- If the DDL statement succeeds, commit the transaction.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage;
-- Test XACT_STATE for 1 or -1.
-- XACT_STATE = 0 means there is no transaction and
-- a commit or rollback operation would generate an error.
-- Test whether the transaction is uncommittable.
IF (XACT_STATE()) = -1
BEGIN
N'The transaction is in an uncommittable state. ' +
'Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
-- Test whether the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
N'The transaction is committable. ' +
'Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH;
GO
TRY…CATCH can be used to handle deadlocks. The 1205 deadlock victim error can be caught by the CATCH block and the transaction can be rolled back until the threads become unlocked. For more information about deadlocking, see Deadlocking.
The following example shows how TRY…CATCH can be used to handle deadlocks. This first section creates a table that will be used to demonstrate a deadlock state and a stored procedure that will be used to print error information.
USE AdventureWorks;
GO
-- Verify that the table does not exist.
IF OBJECT_ID (N'my_sales',N'U') IS NOT NULL
DROP TABLE my_sales;
GO
-- Create and populate the table for deadlock simulation.
CREATE TABLE my_sales
(
Itemid INT PRIMARY KEY,
Sales INT not null
);
GO
INSERT my_sales (itemid, sales) VALUES (1, 1);
INSERT my_sales (itemid, sales) VALUES (2, 1);
GO
-- Verify that the stored procedure for error printing
-- does not exist.
IF OBJECT_ID (N'usp_MyErrorLog',N'P') IS NOT NULL
DROP PROCEDURE usp_MyErrorLog;
GO
-- Create a stored procedure for printing error information.
CREATE PROCEDURE usp_MyErrorLog
AS
'Error ' + CONVERT(VARCHAR(50), ERROR_NUMBER()) +
', Severity ' + CONVERT(VARCHAR(5), ERROR_SEVERITY()) +
', State ' + CONVERT(VARCHAR(5), ERROR_STATE()) +
', Line ' + CONVERT(VARCHAR(5), ERROR_LINE());
ERROR_MESSAGE();
GO
The following code scripts for session 1 and session 2 run simultaneously in two separate SQL Server Management Studio connections. Both sessions try to update the same rows in the table. One of the sessions will succeed with the update operation during the first attempt, and the other session will be selected as the deadlock victim. The deadlock victim error will cause execution to jump to the CATCH block and the transaction will enter an uncommittable state. Inside the CATCH block, the deadlock victim can roll back the transaction and retry updating the table until the update succeeds or the retry limit is reached, whichever happens first.
Session 1 | Session 2 |
|---|---|
USE AdventureWorks; | USE AdventureWorks; |
RAISERROR can be used in either the TRY or CATCH block of a TRY…CATCH construct to affect error-handling behavior.
RAISERROR that has a severity of 11 to 19 executed inside a TRY block causes control to transfer to the associated CATCH block. RAISERROR that has a severity of 11 to 19 executed inside a CATCH block returns an error to the calling application or batch. In this way, RAISERROR can be used to return information to the caller about the error that caused the CATCH block to execute. Error information provided by the TRY…CATCH error functions can be captured in the RAISERROR message, including the original error number; however, the error number for RAISERROR must be >= 50000.
RAISERROR that has a severity 10 or lower returns an informational message to the calling batch or application without invoking a CATCH block.
RAISERROR that has a severity 20 or higher closes the database connection without invoking the CATCH block.
The following code example shows how RAISERROR can be used inside a CATCH block to return the original error information to the calling application or batch. The stored procedure usp_GenerateError executes a DELETE statement inside a TRY block that generates a constraint violation error. The error causes execution to transfer to the associated CATCH block inside usp_GenerateError where the stored procedure usp_RethrowError is executed to raise the constraint violation error information using RAISERROR. This error generated by RAISERROR is returned to the calling batch where usp_GenerateError was executed and causes execution to transfer to the associated CATCH block in the calling batch.
| Note: |
|---|
RAISERROR can generate errors with state from 1 through 127 only. Because the Database Engine might raise errors with state 0, we recommend that you check the error state returned by ERROR_STATE before passing it as a value to the state parameter of RAISERROR. |
USE AdventureWorks;
GO
-- Verify that stored procedure does not exist.
IF OBJECT_ID (N'usp_RethrowError',N'P') IS NOT NULL
DROP PROCEDURE usp_RethrowError;
GO
-- Create the stored procedure to generate an error using
-- RAISERROR. The original error information is used to
-- construct the msg_str for RAISERROR.
CREATE PROCEDURE usp_RethrowError AS
-- Return if there is no error information to retrieve.
IF ERROR_NUMBER() IS NULL
RETURN;
DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200);
-- Assign variables to error-handling functions that
-- capture information for RAISERROR.
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
-- Build the message string that will contain original
-- error information.
SELECT @ErrorMessage =
N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
'Message: '+ ERROR_MESSAGE();
-- Raise an error: msg_str parameter of RAISERROR will contain
-- the original error information.
RAISERROR
(
@ErrorMessage,
@ErrorSeverity,
1,
@ErrorNumber, -- parameter: original error number.
@ErrorSeverity, -- parameter: original error severity.
@ErrorState, -- parameter: original error state.
@ErrorProcedure, -- parameter: original error procedure name.
@ErrorLine -- parameter: original error line number.
);
GO
-- Verify that stored procedure does not exist.
IF OBJECT_ID (N'usp_GenerateError',N'P') IS NOT NULL
DROP PROCEDURE usp_GenerateError;
GO
-- Create a stored procedure that generates a constraint violation
-- error. The error is caught by the CATCH block where it is
-- raised again by executing usp_RethrowError.
CREATE PROCEDURE usp_GenerateError
AS
BEGIN TRY
-- A FOREIGN KEY constraint exists on the table. This
-- statement will generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
END TRY
BEGIN CATCH
-- Call the procedure to raise the original error.
EXEC usp_RethrowError;
END CATCH;
GO
-- In the following batch, an error occurs inside
-- usp_GenerateError that invokes the CATCH block in
-- usp_GenerateError. RAISERROR inside this CATCH block
-- generates an error that invokes the outer CATCH
-- block in the calling batch.
BEGIN TRY -- outer TRY
-- Call the procedure to generate an error.
EXECUTE usp_GenerateError;
END TRY
BEGIN CATCH -- Outer CATCH
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage;
END CATCH;
GO
To change the flow of execution, GOTO can be used within a TRY block or a CATCH block. GOTO can also be used to exit a TRY block or a CATCH block; however, GOTO cannot be used to enter a TRY block or a CATCH block.
The AdventureWorks sample database includes an error-handling solution designed to log information about errors that are caught by the CATCH block of a TRY…CATCH construct that can later be queried or analyzed.
The ErrorLog table records information about an error number, error severity, error state, name of the stored procedure or trigger where the error occurred, line number at which the error occurred, and the complete text of the error message. It also records the date and time at which the error occurred, and the user name which executed the error-generating routine. This table is populated when the stored procedure uspLogError is executed in the scope of the CATCH block of a TRY…CATCH construct. For more information, see ErrorLog Table (AdventureWorks).
The stored procedure uspLogError logs error information in the ErrorLog table about the error that caused execution to transfer to the CATCH block of a TRY…CATCH construct. For uspLogError to insert error information into the ErrorLog table, the following conditions must exist:
The output parameter @ErrorLogID of uspLogError returns the ErrorLogID of the row inserted by uspLogError into the ErrorLog table. The default value of @ErrorLogID is 0. The following example shows the code for uspLogError. For more information, see Stored Procedures in AdventureWorks.
CREATE PROCEDURE [dbo].[uspLogError]
@ErrorLogID [int] = 0 OUTPUT -- Contains the ErrorLogID of the row inserted
-- by uspLogError in the ErrorLog table.
AS
BEGIN
SET NOCOUNT ON;
-- Output parameter value of 0 indicates that error
-- information was not logged.
SET @ErrorLogID = 0;
BEGIN TRY
-- Return if there is no error information to log.
IF ERROR_NUMBER() IS NULL
RETURN;
-- Return if inside an uncommittable transaction.
-- Data insertion/modification is not allowed when
-- a transaction is in an uncommittable state.
IF XACT_STATE() = -1
BEGIN
PRINT 'Cannot log error since the current transaction is in an uncommittable state. '
+ 'Rollback the transaction before executing uspLogError in order to successfully log error information.';
RETURN;
END;
INSERT [dbo].[ErrorLog]
(
[UserName],
[ErrorNumber],
[ErrorSeverity],
[ErrorState],
[ErrorProcedure],
[ErrorLine],
[ErrorMessage]
)
VALUES
(
CONVERT(sysname, CURRENT_USER),
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
ERROR_MESSAGE()
);
-- Pass back the ErrorLogID of the row inserted
SELECT @ErrorLogID = @@IDENTITY;
END TRY
BEGIN CATCH
PRINT 'An error occurred in stored procedure uspLogError: ';
EXECUTE [dbo].[uspPrintError];
RETURN -1;
END CATCH
END;
The stored procedure uspPrintError prints information about the error that caused execution to transfer to the CATCH block of a TRY…CATCH construct. uspPrintError should be executed in the scope of a CATCH block; otherwise, the procedure returns without printing any error information. The following example shows the code for uspPrintError. For more information, see Stored Procedures in AdventureWorks.
CREATE PROCEDURE [dbo].[uspPrintError]
AS
BEGIN
SET NOCOUNT ON;
-- Print error information.
PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) +
', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
', State ' + CONVERT(varchar(5), ERROR_STATE()) +
', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') +
', Line ' + CONVERT(varchar(5), ERROR_LINE());
PRINT ERROR_MESSAGE();
END;
The following example demonstrates the AdventureWorks error-handling solution. The code inside the TRY block tries to delete the record with ProductID 980 in the Production.Product table. A FOREIGN KEY constraint on the table prevents the DELETE statement from succeeding and a constraint violation error is generated. This error causes execution to transfer to the CATCH block. Inside the CATCH block, the following actions occur:
uspPrintError prints the error information. uspLogError enters the error information in the ErrorLog table and returns the ErrorLogID of the inserted row into the @ErrorLogID OUTPUT parameter.USE AdventureWorks;
GO
-- Variable to store ErrorLogID value of the row
-- inserted in the ErrorLog table by uspLogError
DECLARE @ErrorLogID INT;
BEGIN TRY
BEGIN TRANSACTION;
-- A FOREIGN KEY constraint exists on this table. This
-- statement will generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
-- If the delete operation succeeds, commit the transaction.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Call procedure to print error information.
EXECUTE dbo.uspPrintError;
-- Roll back any active or uncommittable transactions before
-- inserting information in the ErrorLog.
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION;
END
EXECUTE dbo.uspLogError @ErrorLogID = @ErrorLogID OUTPUT;
END CATCH;
-- Retrieve logged error information.
SELECT * FROM dbo.ErrorLog WHERE ErrorLogID = @ErrorLogID;
GO
The following example shows using nested TRY…CATCH constructs.
BEGIN TRY
BEGIN TRY
SELECT CAST('invalid_date' AS datetime)
END TRY
BEGIN CATCH
PRINT 'Inner TRY error number: ' +
CONVERT(varchar,ERROR_NUMBER()) + ' on line: ' +
CONVERT(varchar, ERROR_LINE())
END CATCH
SELECT CAST('invalid_int' AS int)
END TRY
BEGIN CATCH
PRINT 'Outer TRY error mumber: ' + CONVERT(varchar,ERROR_NUMBER())+
' on line: ' + CONVERT(varchar, ERROR_LINE())
END CATCH
Here is the result set.
Inner TRY error number: 241 on line: 3
Outer TRY error number: 245 on line: 9
Issue:
If you've a Script Component in the Control Flow, which is writing to LogFile (DtsVariable), and an exception occurs, and if you Exception Handling Component also using the same LogFile (DtsVariable) to log error to the file then you enter a deadlock scenario. It happened because the Script component was still holding the lock but flow jumped to the Exception Handler. All that needs to be done was to un lock variable before we jump to the Exception Handler Block.
Resolution:
Try
...
Catch ex As Exception
Logfile.WriteLine("Exception Occured: " + ex.Message)
Dts.Variables.Unlock()
Dts.TaskResult = Dts.Results.Failure
Throw New Exception(ex.Message)
End Try
Additional Reading:
Reusable Functions for Reading and Writing Variables in SSIS Script Tasks
http://www.developerdotstar.com/community/node/512
