Solved

Error Types

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql sERVER PARSE DATA BY HOURS AND COLUMNS 2 42
Stored Procedure error 45 47
Set the max value for a column 7 35
SQL Server 2012 express 24 34
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…
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

943 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

10 Experts available now in Live!

Get 1:1 Help Now