Click here to Skip to main content
15,867,568 members
Articles / Database Development / SQL Server / SQL Server 2012

SQL server error details.

Rate me:
Please Sign up or sign in to vote.
4.81/5 (18 votes)
22 Sep 2014CPOL4 min read 25.1K   22   11
Overview of SQL server error anatomy.

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.

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

Image 1

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

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

SQL
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

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

How SQL server react to Error?
Below are four main possible action that SQL server take while error occurs.
Generally people have a myth that once error occur in a batch of script next line does not get executed by default.

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.

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

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

Image 2

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.

Image 3

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.

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

Image 4

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.

SQL
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

SQL
EXEC XactAbortONSP

Below is output.

Image 5

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.

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

SQL
EXEC XactAbortOFFSP

Image 6

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionAwesome Article Pin
Member 114657794-Mar-15 1:52
Member 114657794-Mar-15 1:52 
AnswerRe: Awesome Article Pin
Jitendra Ku. Sahoo9-Mar-15 21:51
Jitendra Ku. Sahoo9-Mar-15 21:51 
GeneralMy vote of 5 Pin
Humayun Kabir Mamun24-Sep-14 22:03
Humayun Kabir Mamun24-Sep-14 22:03 
GeneralRe: My vote of 5 Pin
Jitendra Ku. Sahoo28-Sep-14 23:15
Jitendra Ku. Sahoo28-Sep-14 23:15 
QuestionGood Article Pin
Sudhansu Sekhar Prusty22-Sep-14 19:49
professionalSudhansu Sekhar Prusty22-Sep-14 19:49 
AnswerRe: Good Article Pin
Jitendra Ku. Sahoo28-Sep-14 23:15
Jitendra Ku. Sahoo28-Sep-14 23:15 
GeneralRe: Good Article Pin
Sudhansu Sekhar Prusty29-Sep-14 20:44
professionalSudhansu Sekhar Prusty29-Sep-14 20:44 
GeneralWell composed Pin
Dukhabandhu Sahoo22-Sep-14 5:59
professionalDukhabandhu Sahoo22-Sep-14 5:59 
GeneralRe: Well composed Pin
Jitendra Ku. Sahoo28-Sep-14 23:14
Jitendra Ku. Sahoo28-Sep-14 23:14 
QuestionNice. Pin
Smrutiranjan Parida22-Sep-14 3:03
professionalSmrutiranjan Parida22-Sep-14 3:03 
AnswerRe: Nice. Pin
Jitendra Ku. Sahoo28-Sep-14 23:13
Jitendra Ku. Sahoo28-Sep-14 23:13 
Thanks Smile | :)

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.