SET option XACT_ABORT and SQL Server Transaction

SET option XACT_ABORT and SQL Server Transaction

Error is something which nobody wants to have in their software/code though, as a developer, we used to see errors so many times no matter whether it is development environment or live environment.

If we talk specifically about SQL Server, we used to come across following types of errors in SQL Server.

1.) Statement Terminate:

2.) Scope Terminate:

3.) Batch Terminate:

4.) Connection Terminate:

People tend to use “TRANSACTION” when they are using DML command, especially more then one DML operation based on each other. This is completely fine. I, too, tend to use it but we never like to hang the transaction as it is. We may want to ROLLBACK all DML statement if any run-time error come across in-between execution of any DML statement given in one batch.

We may use “ROLLBACK” statement and TRY…CATCH to manage our transaction even sometime bigger severity runtime error occurs and your code don’t follow the CATCH block either and hence ROLLBACK TRANSACTION never executes so If we have XACT_ABORT option set to ON, we can be sure that if run-time error occurs, we will have all DML transaction rolled back.

Let us understand this by following example:

Creating sample temporary table with some sample data:

[sourcecode language=”sql”]
IF OBJECT_ID(‘tempdb..#TestingTHROWCommand’) IS NOT NULL
DROP TABLE #TestingTHROWCommand

CREATE TABLE #TestingTHROWCommand
(
ID INT IDENTITY(1,1)
,Name VARCHAR(50)
,OvertimeAmount INT
)

INSERT INTO #TestingTHROWCommand
SELECT ‘Ritesh Shah’,15 UNION ALL
SELECT ‘Teerth Shah’,0 UNION ALL
SELECT ‘Rajan Jain’,9
GO
[/sourcecode]

Now we will execute one TSQL code block which will update our temp table twice. First UPDATE statement is fine but second will generate the error. Keep in mind that we neither have ROLLBACK nor we have XACT_ABORT ON in following block:

[sourcecode language=”sql”]
BEGIN TRY
BEGIN TRANSACTION

UPDATE #TestingTHROWCommand
SET OvertimeAmount=95/OvertimeAmount
WHERE ID=1

UPDATE #TestingTHROWCommand
SET OvertimeAmount=95/OvertimeAmount
WHERE ID=2

COMMIT TRANSACTION
END TRY

BEGIN CATCH
SELECT ERROR_NUMBER(),ERROR_MESSAGE(),ERROR_LINE()
END CATCH
GO
[/sourcecode]

Can you imagine what would happens to both the UPDATE? Let us see what happened to both UPDATE by executing simple SELECT statement on temp table.

[sourcecode language=”sql”]
SELECT * FROM #TestingTHROWCommand
GO
[/sourcecode]

Here is the output I have received for my SELECT statement.

WithoutXACT_Abort

You can see that there wasn’t any error in first UPDATE so that value is updated and second UPDATE had an error and hence it is not updated obviously. We may want that if error occurs, neither of the records should be updated. We might use CATCH block for ROLLBACK TRANSACTION but what, if error was with high severity and it doesn’t falls under CATCH statement? So it is better to be in safe side and have XACT_ABORT with ON status in every code block wherever we are using DML statements with TRANSACTION.

Let us insert all original values in our temp table.

[sourcecode language=”sql”]
TRUNCATE TABLE #TestingTHROWCommand
GO

INSERT INTO #TestingTHROWCommand
SELECT ‘Ritesh Shah’,15 UNION ALL
SELECT ‘Teerth Shah’,0 UNION ALL
SELECT ‘Rajan Jain’,9
GO
[/sourcecode]

Now we can execute same UPDATE statements with XACT_ABORT.

[sourcecode language=”sql”]
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION

UPDATE #TestingTHROWCommand
SET OvertimeAmount=95/OvertimeAmount
WHERE ID=1

UPDATE #TestingTHROWCommand
SET OvertimeAmount=95/OvertimeAmount
WHERE ID=2

COMMIT TRANSACTION
END TRY

BEGIN CATCH
SELECT ERROR_NUMBER(),ERROR_MESSAGE(),ERROR_LINE()
END CATCH
GO
[/sourcecode]

As soon as I ran above code block, I greeted with following error:

WithXACT_Abort

[sourcecode language=”sql”]
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
[/sourcecode]

Can you imagine what would happens to both the UPDATE? Let us see what happened to both UPDATE by executing simple SELECT statement on temp table.Here is the output I have received for my SELECT statement.

[sourcecode language=”sql”]
SELECT * FROM #TestingTHROWCommand
GO
[/sourcecode]

WithXACT_AbortSELECT

You can see that none of the values are updated. XACT_ABORT did ROLLBACK by its own even without explicitly written ROLLBACK statements. I am not saying that don’t put ROLLBACK statements. It MUST be there but XACT_ABORT can be useful in certain situation we might have missed to handle.

If you are interested to read error handling article I have written previously, have a look at list below:

Error handling with “THROW” command in SQL Server 2012 (Click Here)
List of Errors and severity level in SQL Server with catalog view sysmessages (Click Here)
Create custom error message by sys.sp_addmessage in SQL Server 2012 (Click Here)
Importance of transaction in SQL Server (Click Here)
Capture and log error of TSQL code in SQL Server 2012 (Click Here)
SET option XACT_ABORT and SQL Server Transaction (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles

Capture and log error of TSQL code in SQL Server 2012

Capture and log error of TSQL code in SQL Server 2012

SQL Server provides a robust way to capture error with TRY….CATCH but I wanted to have centralized information about error to maintain history and for debugging purpose so that I have created one stored procedure to log error information in one table. I used to call that stored procedure which capture error in my every stored procedure.

I have created “[dbo].[usp_LogErrorHistory] ” stored procedure which logs all errors in “[dbo].[ErrorHistory]” table. I used to call “[dbo].[usp_LogErrorHistory] ” SP in every stored procedure I create and hence I have, now, one central location (“[dbo].[ErrorHistory]” table) where I can go and look for total number of error withing given time period and for given objects.

Let us create “[dbo].[ErrorHistory]” table and “[dbo].[usp_LogErrorHistory] ” stored procedure with following TSQL:

[sourcecode language=”sql”]
CREATE TABLE [dbo].[ErrorHistory](
[ErrorLogID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[ErrorTime] [datetime] NOT NULL DEFAULT GETDATE(),
[UserName] [sysname] NOT NULL,
[ErrorNumber] [int] NOT NULL,
[ErrorSeverity] [int] NULL,
[ErrorState] [int] NULL,
[ErrorProcedure] [nvarchar](126) NULL,
[ErrorLine] [int] NULL,
[ErrorMessage] [nvarchar](4000) NOT NULL,
[ContextInfo] [varbinary](max) NULL,
CONSTRAINT [PK_ErrorLog_ErrorLogID] PRIMARY KEY CLUSTERED
(
[ErrorLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO

CREATE PROCEDURE [dbo].[usp_LogErrorHistory]
— contains the ErrorLogID of the row inserted
@ErrorLogID [int] = 0 OUTPUT
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 usp_LogErrorHistory in order to successfully log error information.’;
RETURN;
END

INSERT [dbo].[ErrorHistory]
(
[UserName],
[ErrorNumber],
[ErrorSeverity],
[ErrorState],
[ErrorProcedure],
[ErrorLine],
[ErrorMessage],
[ContextInfo]
)
VALUES
(
CONVERT(sysname, CURRENT_USER),
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
ERROR_MESSAGE(),
CONTEXT_INFO()
);

— Pass back the ErrorLogID of the row inserted
SET @ErrorLogID = @@IDENTITY;
END TRY
BEGIN CATCH
PRINT ‘An error occurred in stored procedure usp_LogErrorHistory: ‘;
RETURN -1;
END CATCH
END;
GO
[/sourcecode]

Once we are done with above given SP and table, we will create one more user stored procedure which we will execute and it will generate an error which will be logged in our error history table.

[sourcecode language=”sql”]
CREATE PROC TestErrorLog1
AS
BEGIN
–Specifies whether SQL Server automatically rolls back the current transaction
–when a Transact-SQL statement raises a run-time error.
–this is the reason I keep having XACT_ABORT in all my SP when transaction is used
SET XACT_ABORT ON;

–used to keep following statement in all my SP for performance point of view
SET NOCOUNT ON

BEGIN TRY
–I am not performing any DML statement here though I have kept transaction here
–to demonstrate the usage
BEGIN TRANSACTION
SELECT 1/0
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT>0
ROLLBACK

–to log the error
EXEC [dbo].[usp_LogErrorHistory] ;

–to show the error to the end user
THROW
END CATCH
END
GO
[/sourcecode]

Now this is a time to check functionality of our error SP by executing following TSQL script:

[sourcecode language=”sql”]
–execut TestErrorLog1 SP to confirm whether error is being logged or not
EXECUTE TestErrorLog1
GO
[/sourcecode]

Okay so I hope your first error after creating this environment has logged. Let us confirm it by executing SELECT query in Error History table.

[sourcecode language=”sql”]
–Looking at [dbo].[ErrorHistory] table to see captured error:
SELECT * FROM [dbo].[ErrorHistory]
[/sourcecode]

Here is the output of my SELECT statement on ErrorHistory table.

1Error

Please note that This code should work in older version of SQL Server eg: SQL Server 2008 or 2008 R2 as well. You have to use RAISERROR instead of THROW statement.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Importance of transaction in SQL Server

Importance of transaction in SQL Server

Recently I have written few articles on error handling (list given in the last paragraph of this article) topic whereas I have used Transaction in my TSQL script there and have promised blog reader that I will come up with more details and explanation of transaction. Finally today I thought to write about transaction.

Transaction is one of the most important part for developer to be master in development. We might have cases where we are doing more then one combination of INSERT / UPATE / DELETE (DML statements) in TSQL code block. It is quite possible that any of the DML operation failed in-between execution and we want to revert back any DML changes made prior from that failure to keep consistency and integrity. Transaction comes to your help for this need and makes your database ACID (Atomicity, Consistency, Isolation and Durability) compliant (Click here to know more about ACID.).

According to me, there should be Begin Transaction, Commit Transaction and ROLLBACK Transaction with each TSQL block if it is using DML statements, no matter whether code block has one or more DML operation going.

Transaction creates locking so it is also advisable to keep your transaction as short as possible to avoid locking and blocking issue.

Let us see how we can use transaction in TSQL code block. We will need to table with some data for demonstration. Let us create two table and define foreign key between them.

[sourcecode language=”sql”]
CREATE TABLE tblEmployeeDetail
(
ID INT IDENTITY(1,1) CONSTRAINT pk_tblEmployeeDetail_Id PRIMARY KEY
,EmployeeName Varchar(100)
,Designation VARCHAR(50)
,City Varchar(20)
)
GO

CREATE TABLE tblEmployeeSalary
(
ID INT IDENTITY(1,1)
,EmployeeID INT CONSTRAINT fk_tblEmployeeDetail_id FOREIGN KEY REFERENCES tblEmployeeDetail(ID)
,Salary INT NOT NULL
)
GO

INSERT INTO tblEmployeeDetail
SELECT ‘Ritesh Shah’,’DBA’,’Ahmedabad’ UNION ALL
SELECT ‘Teerth Shah’,’Developer’,’Ahmedabad’
GO

INSERT INTO tblEmployeeSalary
SELECT 1,50000 UNION ALL
SELECT 2,45000
GO[/sourcecode]

Execute following UPDATE statements which will ROLLBACK transaction when error come across.

[sourcecode language=”sql”]
–We are now going to update designation in EmployeeDetail table
–and update the salary. if there is an issue in any of the UPDATE statement
–both table should be in the previous state
BEGIN TRY
BEGIN TRANSACTION
UPDATE tblEmployeeDetail SET Designation=’DBA Manager’ WHERE ID=1

–giving NULL value though it is not allowed in SALARY field (as it is NOT NULL field)
–it will generate an error and the Designation in tblEmployeeDetail table will also revert back
UPDATE tblEmployeeSalary SET Salary=NULL WHERE EmployeeID=1
COMMIT TRANSACTION
END TRY
BEGIN CATCH
declare @trancount int;
set @trancount = @@trancount;
IF @trancount>0
BEGIN
ROLLBACK;
THROW
END
END CATCH
GO

–Confirm whether any record is changed or not
SELECT * FROM tblEmployeeDetail
SELECT * FROM tblEmployeeSalary
GO
[/sourcecode]

Here is the error I have received while executing UPDATE code block.

1Error

Please note that This code should work in older version of SQL Server eg: SQL Server 2008 or 2008 R2 as well. You have to use RAISERROR instead of THROW statement.

If you are interested to read error handling article I have written previously, have a look at list below:

Error handling with “THROW” command in SQL Server 2012 (Click Here)
List of Errors and severity level in SQL Server with catalog view sysmessages (Click Here)
Create custom error message by sys.sp_addmessage in SQL Server 2012 (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Create custom error message by sys.sp_addmessage in SQL Server 2012

Create custom error message by sys.sp_addmessage in SQL Server 2012

error

Social media is a wonderful way to share knowledge. I have my own Facebook page where I used to share my knowledge. Yesterday I see one message in my Facebook inbox. One SQL Server community member asked me whether it is possible to create our own error messages in SQL Server or not. He told me that after reading two of my previous articles (Error handling with “THROW” command in SQL Server 2012 and List of Errors and severity level in SQL Server with catalog view sysmessages) related to error handling, he has looked at catalog view “Master.DBO.SysMessages” and he wanted to define his own custom error messages.

Microsoft have given control to us to create custom messages as per our need and system stored procedure “sys.sp_addmessage” helps us to do this.

Let us look at one example for this:

[sourcecode language=”sql”]
–I want to add error with error number 50001
–let us check whether any error is having same number or not
–in my case, I don’t find any error with 50001 number
select * from master.dbo.sysmessages where error =’50001′;

–adding error message with 50001 number and severity 16
–if record duplicate records comes, we can execute this error
EXEC sys.sp_addmessage 50001, 16, ‘%s is already there in table %s.’;
GO

–now we will get one row for default language (in my case it is 1033 which is US English)
–with 50001 number
select * from master.dbo.sysmessages where error =’50001′;
[/sourcecode]

Now, we have one custom message ready with us, let us check it by creating one sample table with some sample data row.

[sourcecode language=”sql”]

–now testing the error message whether it is working fine or not.
BEGIN TRY
DECLARE @Name VARCHAR(50),@City AS VARCHAR,@Count INT
SET @Name=’Rajan Shah’
SET @City=’Mumbai’

SELECT @Count=COUNT(1) FROM TestCustomError WHERE Name=@Name

–In this case, I have considered that Name column should be unique
–there may or may not be Primary or Unique Key defined
–but we can test it in business logic
–there may be argument for this approach but I just wanted to show
–whether custom error is working or not.
IF @Count<=0
BEGIN
BEGIN TRANSACTION
INSERT INTO TestCustomError (Name,City)
SELECT @Name,@City
COMMIT TRANSACTION
END
ELSE
BEGIN
DECLARE @ErrMessage varchar(500) = FORMATMESSAGE(50001, @Name, ‘TestCustomError’);
THROW 50001, @ErrMessage, 1;
END
END TRY

BEGIN CATCH
THROW;
END CATCH
GO
[/sourcecode]

Scope of this article was to show how to add custom error message and check it. I have kept one loophole by not putting ROLLBACK anywhere in the code which itself is an interesting topic and out of the scope of this article. I will cover this point very soon.

Please note that This code should work in older version of SQL Server eg: SQL Server 2008 or 2008 R2 as well. You have to use RAISERROR instead of THROW statement.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Error handling with “THROW” command in SQL Server 2012

Error handling with “THROW” command in SQL Server 2012

error

Error handling is one of the essential skill developer should have. It was very difficult to handle error efficiently till SQL Server 2000. After SQL Server 2000, we have get TRY…CATCH in SQL Server along with RAISERROR in SQL Server 2005. RAISERROR is even improved in form of “THROW” in SQL Server 2012.

Let us see how “THROW” command works for error handling efficiently in SQL Server 2012 because without know the error/bug in application, you can’t solve it. It is not possible/feasible, sometime, in big production environment to replicate same issue in development database, at the sametime, we can’t execute some command/query on live environment if it is affecting client’s data so efficient error handling is required so that you can log proper error along with its message, error number and other important things.

Let us create sample temporary table in AdventureWorks2012 database, if you don’t have Adventureworks2012 database with you, you can use your own database as I will be having temp table for this demonstration.

[sourcecode language=”sql”]
IF OBJECT_ID(‘tempdb..#TestingTHROWCommand’) IS NOT NULL
DROP TABLE #TestingTHROWCommand

CREATE TABLE #TestingTHROWCommand
(
ID INT IDENTITY(1,1)
,Name VARCHAR(50)
,OvertimeAmount INT
)

INSERT INTO #TestingTHROWCommand
SELECT ‘Ritesh Shah’,15 UNION ALL
SELECT ‘Teerth Shah’,0 UNION ALL
SELECT ‘Rajan Jain’,9
GO[/sourcecode]

Now let us try to execute one UPDATE statement where we will have one division operator which divide on “OvertimeAmount” field. We have value “0” with row number 2 so obviously we will be facing an error.

[sourcecode language=”sql”]
BEGIN TRY
BEGIN TRANSACTION
UPDATE #TestingTHROWCommand
SET OvertimeAmount=95/OvertimeAmount
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER(),ERROR_MESSAGE(),ERROR_LINE()
ROLLBACK TRANSACTION
END CATCH
GO[/sourcecode]

As soon as we will execute above code, we will be greeted with an error because on row # 2, we have 0 which will try to divide 95 and 0 can’t be used to divide anything. Here is the error message we will get:

[sourcecode language=”sql”]
(0 row(s) affected)
8134 Divide by zero error encountered. 3
[/sourcecode]

Look at the error number, message and error line number given above. Now, we will try to handle error with different way as follow:

[sourcecode language=”sql”]
BEGIN TRY
BEGIN TRANSACTION
UPDATE #TestingTHROWCommand
SET OvertimeAmount=95/OvertimeAmount
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE()
ROLLBACK TRANSACTION
RAISERROR(@ErrorMsg,16,1)
END CATCH
GO[/sourcecode]

We have same UPDATE statement so obviously we will be getting error message again but this time, it will come by “RAISERROR” command. Here is the output of RAISERROR.

[sourcecode language=”sql”]
(0 row(s) affected)
Msg 50000, Level 16, State 1, Line 10
Divide by zero error encountered.[/sourcecode]

Look at the error number and error line. It seems wrong because of “RAISERROR”. Let us now try to handle the error with “THROW” command.

[sourcecode language=”sql”]
BEGIN TRY
BEGIN TRANSACTION
UPDATE #TestingTHROWCommand
SET OvertimeAmount=95/OvertimeAmount
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
THROW
END CATCH
GO[/sourcecode]

we will again receive proper error message, number and line with “THROW” command. Have a look:

[sourcecode language=”sql”]
(0 row(s) affected)
Msg 8134, Level 16, State 1, Line 3
Divide by zero error encountered.[/sourcecode]

I have recently seen many of the developer who are using SQL Server 2012, still uses “RAISERROR” as against “THROW”. I would highly recommend start using “THROW” command.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.