Solved

Error Types

Posted on 2009-04-02
2
279 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 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
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…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

691 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