Solved

Error Types

Posted on 2009-04-02
2
273 Views
Last Modified: 2012-05-06
I am looking into error severity within SQL Server 2005

I am interested in how severity relates to how stored prcocedures are halted.

Consider the table and stored prcoedure as attached.

Now if I run this then 3 records will be inserted.

If however I deliberatley sabotage the first insert so that an error is raised then we can get scenarios where the stored prcoedure continues and another scenario where it halts completley.

If we change the 1st line to:

Insert Into tbl_People (Firstname, Surname, Age) Values ('Tiffany', 'Smith', Null)

Then we will get this:

Msg 515, Level 16, State 2, Procedure Test, Line 5
Cannot insert the value NULL into column 'Age', table 'DacServices.dbo.tbl_People'; column does not allow nulls. INSERT fails.
The statement has been terminated.

(1 row(s) affected)

(1 row(s) affected)

So the first insert fails and the other 2 work ok.

If however I change the first line to this:

Insert Into tbl_PeopleXXXX (Firstname, Surname, Age) Values ('Tiffany', 'Smith', 22)

Then this time an error is raised again but the stored procedure is stopped dead in it's tracks and the other two inserts aren't even attempted.  the error we get is:

Msg 208, Level 16, State 1, Procedure Test, Line 5
Invalid object name 'tbl_PeopleXXXX'.

Ok so that's all fine and makes sense but what is confusing me is that both of these errors have a severity of 16.

Now I thought that error severity numbers were 1-10 for warnings, 11-16 for errors and 17-25 for serious errors which will stop the code in its tracks and kill the connection.

So what is it between these two errors with severity of 16 which SQL Server uses to decide whether to continue running the stored procedure?  That is my question.  I am thinking I migth write code which Raises an error and would like to be able to Raise the type which behave in these two ways.
USE [DacServices]

GO

/****** Object:  Table [dbo].[tbl_People]    Script Date: 04/02/2009 10:30:55 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[tbl_People](

	[PersonID] [int] IDENTITY(1,1) NOT NULL,

	[Firstname] [varchar](50) NOT NULL,

	[Surname] [varchar](50) NOT NULL,

	[Age] [int] NOT NULL,

 CONSTRAINT [PK_tbl_People] PRIMARY KEY CLUSTERED 

(

	[PersonID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]
 

GO

SET ANSI_PADDING OFF
 

------------------------------------------------------------------------------
 

USE [DacServices]

GO

/****** Object:  StoredProcedure [dbo].[Test]    Script Date: 04/02/2009 10:34:54 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER Procedure [dbo].[Test]
 

As
 

Insert Into tbl_People (Firstname, Surname, Age) Values ('Tiffany', 'Smith', 22)
 

Insert Into tbl_People (Firstname, Surname, Age) Values ('Fred', 'Williams', 33)
 

Insert Into tbl_People (Firstname, Surname, Age) Values ('Dave', 'Browning', 44)

Open in new window

0
Comment
Question by:daveamour
2 Comments
 
LVL 11

Accepted Solution

by:
Salim Fayad earned 500 total points
ID: 24048729
0
 
LVL 19

Author Comment

by:daveamour
ID: 24048775
Ok thanks so this is the key bit from that link:
You might be wondering what actions cause fatal errors. Unfortunately, the actions that cause a fatal error are not well documented. Each error has an associated severity level that is a value between 025. The errors with a severity level of 20 or above are all fatal, but once you get below this value there is no well-defined rule as to which errors are fatal. In truth, though, worrying about which errors are fatal is a bit useless because there is no code you can implement that will allow you to handle them gracefully.
But I don't like that answer, true thought it may be!
I'll accept this as an answer but would just like to leave the question open a little while to see if anyone else has any ideas.  After all when we get right down into the heart of the SQL server code there is an if statement somewhere which is deciding whether an error is fatal or not.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now