SQL server error details.

Introduction

Exception are unavoidable in any programming. To make our application robust, we need to implement some way to handle error. So we need to take care of error handling in our database script too. This article focus on SQL server error, how it react to error. In next article we will have a nice template to manage error in stored procedure and function.

Let us dissect the Error

Lets us start with ERROR MESSAGE anatomy.

1: ERROR MESSAGE NUMBER: 
Each error have a unique number. We can find all system defined error from sysmessages table under Master database. Error number < 50K are system defined message. By default RAISERROR has error message number as 50000.

RAISERROR('This is an error', 16, 1)

We can add our custom error message by using system defined sp sp_addmessge.

SELECT * FROM sysmessages
EXEC sp_addmessage @msgnum = 50003, @severity = 11, @msgtext='My Own Error'
SELECT * FROM sysmessages

We can use sp_dropmessage to remove our custom message.

SELECT * FROM sysmessages
EXEC sp_dropmessage @msgnum = 50003
SELECT * FROM sysmessages

2: SEVERITY LEVEL: 
It is a number between 1 to 25.      
0-10 are warning or informative error.
11-16 to show programming error.
17-25 show Resource / Hardware /OS/ SQL Server Internal Errors

Error message with higher severity level are considered as fatal. Here is a nice article on it from here
 

3:ERROR STATE: 
It is an interger value. If same error raised at multiple point of code, we can user error state to distinguish the part of script that raise the error
Lets say we have similar condition like below

IF <condition1>
  RAISERROR(N'Error raised for condition1', 16, 0);
IF <condition2>
  RAISERROR(N'Error raised for condition2', 16, 1);
GO

4:PROCEDURE: 
This show which stored procedure, user defined function or trigger raised the error. If error occur for a batch statement, then this is BLANK.

5:ERROR LINE: 
This show line number of the Statement within SP/UDF/Batch which caused the error. It will be 0 If SP/UDF Invoke Causes the Error.

6:ERROR MESSAGE: 
This is the most important thing for a developer. It show actual error message.

1: STATEMENT TERMINATION :
 SQL server terminate the statement which caused the error but continue to execute next statement. If there is  any open transaction that is not rolled back i.e transactions are not affected by it. Duplicate primary  key, NOT NULL violation, Violation of CHECK constraint or FOREIGN KEY constraint, Permission  denied to table or SP, Arithmetic overflow error are few error that leads to statement termination.

 Let us have a small demo.

 Let us create a table that we can use for all demo purpose.

--Create a Table Book 
CREATE TABLE dbo.Book
(   
 BookID INT NOT NULL PRIMARY KEY, 
 Name   NVARCHAR (100) NOT NULL,
 Price  Money NOT NULL
)
GO

Let us insert data into this table with duplicate PRIMARY KEY value.

--Here we will get statement Abortion
 PRINT 'Beginning  of execution'
 INSERT INTO Book(BookID, Name , Price)
 VALUES(1, 'Demo Book1',  100)
 INSERT INTO Book(BookID, Name , Price)
 VALUES(1, 'Demo Book2',  200)
 PRINT 'End  of execution'

Here error occured on 2nd INSERT statement, but last PRINT statement executed. Below is output.

2: SCOPE ABORTION:
  Here SQL server terminate all the statement in current scope and continue to execute all the statement outside     the scope that raised the error. Current scope means stored procedure, user-defined function, or block of SQL       statements, including dynamic SQL.

  Lets have a demo.

  --Here we will get Scope Abortion
PRINT 'Beginning  of Scope execution'
EXEC('SELECT BookID FROM BOOKs') --SELECT query executed dynamically.
PRINT 'End  of Scope execution'
GO

 Here we are trying to select data from a non-existing table using dynamic SQL. Dynamic script execution takes  the script to another scope, as a result we get statement termination error action and final PRINT statement  executed.

3:BATCH TERMINATION:
 The execution of entire client call is terminated. If there is any open transaction exists those are rolled back.This  mainly happens for Most conversion errors like when we try to convert non-numeric string to  Number, Dead  Lock issue, Mismatch number of parameter while executing SP or inserting data to table.

--Here we will get Batch Abortion
PRINT 'Beginning  of Batch execution'
SELECT BookID FROM BOOKs
PRINT 'End  of Batch execution'
GO

This leads to batch abortion and last statement not-executed.

4:CONNECTION TERMINATION:
 This error action occurs for error severity levels 20-25. Here the client is disconnected and any open transaction is  rolled back. This happens only when some bad thing happens to SQL server like hardware  problems, network  problems, database corruption or severe resource problems.

5: XACT_ABORT:
   We can set it ON or OFF.The default is OFF.So here SQL server will decide what to do depending upon severity      level of error. SET XACT_ABORT ON instruct SQL server to terminate entire transaction.
   Lets have a small demo on it.

CREATE PROCEDURE dbo.XactAbortONSP
AS
BEGIN
    SET XACT_ABORT ON
    PRINT 'Beginning of execution'
    EXEC MissingSP --This SP doesnot exist
    PRINT 'End of execution'
END

Let us run the SP

EXEC XactAbortONSP

Below is output.

Here we attempt to execute non-existing stored procedure which result into Statement Termination.
But we have set  SET XACT_ABORT ON, so it terminate whole batch of statement.

Now let check with XACT_ABORT OFF demo.

CREATE PROCEDURE dbo.XactAbortOFFSP
AS
BEGIN
    SET XACT_ABORT OFF
    PRINT 'Beginning of execution'
    EXEC MissingSP --This SP doesnot exist
    PRINT 'End of execution'
END

Let run the sp.

EXEC XactAbortOFFSP

Here we get Statement Termination error action.  In summary we can say that SET XACT_ABORT ON makes Statement termination error action to Batch Termination error action.

Conclusion.

I think we have learned something new from this post. Please  correct me if there is any mistake, so that I can correct it. This is my maiden article. So I need your suggestions and feedback to improve the quality of not only this article but also upcoming articles. Please have a look on my tips "Template to handle error in SQL server"  to the template to handle the error.

History

Keep a running update of any changes or improvements you've made here.