Solved

Error Types

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Stored procedure 4 37
CONVERT date time to a different time zone. 2 71
Query 14 59
Email Notifications for SQL 2005 9 37
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

749 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