?
Solved

Error Types

Posted on 2009-04-02
2
Medium Priority
?
282 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 11

Accepted Solution

by:
Salim Fayad earned 2000 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

777 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