SQL Server

SQL Server 2008 : Implementing Error Handling - Understanding Errors

11/14/2010 4:14:35 PM
Basically, SQL Server returns two types of messages: information and error. An information message is a return of some information to users. This type of message is informational in nature and doesn’t create errors at runtime. For example, when you execute a check in your database with a DBCC CHECKDB statement, an informational message related to the database is returned. This type of message might also be returned as output from PRINT and DBCC statements, as a message citing an error from sys.messages catalog view, or as a RAISERROR statement.

An error message is a return of a warning message that informs you about problems that affect the execution of your statement. For example, when your statement calls an object that doesn’t exist, SQL Server generates and returns an error message then terminates execution of the statement. These error messages are returned only as messages citing errors from sys.messages catalog view or as a RAISERROR statement. You can use these messages to handle various types of errors: errors that stop your code from proceeding further; errors that raise warnings, but continue the execution of your code; and errors that belong to the business logic of your application and are not SQL Server errors.

Although information messages are not exactly error messages, they are handled and managed the same way as error messages. The only difference between them is severity, which is further discussed later in this chapter.

Error Types

There are two types of errors in SQL Server: system and user-defined. System errors arise when the Database Engine service encounters an error in code execution. This service provides objects and structures that allow the return of system errors or messages according to executed code. The Database Engine verifies the code of each operation and returns a status indicating the success or failure of its execution. When an operation fails, the service picks the error information from the sys. messages catalog view and shows it to the user. SQL Server raises system errors automatically when executing an operation. However, you can handle these errors using T-SQL commands.

Although SQL Server provides a significant set of error messages, you may face situations when SQL Server doesn’t recognize parts of your code as errors but you wish to raise them as errors. These customized errors are called user-defined errors, and they must be raised manually. For example, you create a procedure that inserts a product name and stock inside a table. Although the stock column can accept any INT value, your business requirement specifies that a product must be added with a minimum stock level of 100. So, your procedure checks the stock input parameter, and, if the value is less than the required minimum, it will return an error. In this case, you define the error and write code that raises it every time a validation fails.

sys.messages Catalog View

SQL Server stores system and user-defined errors inside a catalog view called sys.messages. This catalog contains all system error messages, which are available in many languages. Each message has five properties arranged in columns:

  • message_id: This column stores the ID of a message and its value. Together with language_id, this is unique across the instance. Messages with IDs less than 50000 are system error messages.

  • language_id: This column indicates the language used in the message text, for example English or Spanish, as defined in the name column of the sys.syslanguagesmessage_id. system table. This is unique for a specified

  • severity: This column contains the severity level of the message. When you are creating errors, keep in mind that the severity must be the same for all message languages within the same message_id.

  • is_event_logged: When you want to log the event when an error is raised, set the value of this column equal to1. Like severity, this column must be the same for all message languages within the same message_id.

  • text: This column stores the text of the message. This text is written in the language indicated by the language_id property.

Severity Level

The severity level of an error indicates how critical the impact of this error is on the execution of your operation. The level indicated also helps you understand the scope of the problem. The severity levels for SQL Server range from information message to system and server level problems and are distributed into four major groups:

  • Information Messages (Levels 1–10): Errors in this group are purely informational and are not severe. Status information or error reports are returned with a specific error code. SQL Server doesn’t use levels 1–9: these levels are available for user-defined errors.

  • User Errors (Levels 11–16): Errors in this group are quite simple and can be corrected by you or the user. They don’t impact service or terminate connection with the database.

  • Software Errors (Levels 17–19): Errors in this group are severe and require system administrator attention as well as yours. They are related to problems in the Database Engine service and can’t be solved by users.

  • System Errors (Levels 20–25): Errors in this group are critical since they indicate system problems. They are fatal errors that force the end of the statement or batch in the Database Engine. Errors in this group record information about what occurred and then terminates. You must be aware that these errors can close the application connection to the instance of SQL Server. Error messages in this severity level group are written to the error log.

Severity levels are important not only to help you diagnosis the impact of a problem but also to help you to organize and manage user-defined errors according to database needs.

Scenarios of Use

Error handling provides efficient ways of controlling flow and debugging code, making the execution of operations more predictable and providing proactive solutions. For example, you can handle an error that arises from a transaction deadlock by returning a message and logging the error in a table of your database for posterior analysis. Also, you can debug a long string of transaction code without the application being available by programming the code to raise errors and show the line and statement where the code fails. Another advantage of handling errors inside SQL Server is that you reduce the application’s workload by distributing it among servers, thereby letting developers focus on business requirements instead of SQL Server commands and transactions.

In addition to system errors, you can also create and handle user-defined errors to customize and standardize the return of procedures, functions, and other programmable objects in your database. This gives you a centralized, organized, and effective way to attend to business requirements. For example, you can create a customized error message about the failure of a business validation. After the message is created, you can raise the error whenever you want. Also, when you make changes to the error, your modifications are applied to all codes that call it. Another great advantage of user-defined errors inside SQL Server is that they are independent from the programming language of your application. If you have an application that was developed with language A and you decide to develop a new one using language B, you won’t need to recreate new error messages. Because the messages are stored inside SQL Server, they are available to both applications. You can further enhance this scenario by creating a library of user-defined errors that are shared among multiple SQL Server databases in the same environment, providing efficient management across multiple applications.

Other -----------------
- Implementing SQL Server Objects Using Managed Code (part 2)
- Implementing SQL Server Objects Using Managed Code (part 1)
- Encryption Catalog Views
- Built-In Cryptographic Functions
- SQL server 2008 : Managing Security - Permissions
- SQL server 2008 : Managing Security - Schemas
- SQL server 2008 : Managing Security - Users
- SQL server 2008 : Managing Security - Roles
- SQL Server 2008 : Managing Remote Servers
- Linked Servers
- Adding, Dropping, and Configuring Linked Servers
- Mapping Local Logins to Logins on Linked Servers
- Obtaining General Information About Linked Servers
- Executing a Stored Procedure via a Linked Server
- Setting Up Linked Servers Using SQL Server Management Studio
- Encryption basics for SQL Server : Cryptographic Keys
- Encryption basics for SQL Server : Key Maintenance
- Encryption basics for SQL Server : Key Algorithms
- SQL Server 2005 : Performing Database Backups
- SQL Server 2005 : Restoring Data from a Backup
Most View
- Sharepoint 2010 : Excel Service - Demonstration Scenario (part 3)
- SQL Azure : Design Patterns (part 3)
- Windows Server 2008 : Configuring FTP (part 5)
- Protecting SQL Server Data : Preparing for Cell-Level Encryption
- Visual Basic 2010 : Deploying Applications with ClickOnce - Configuring ClickOnce
- SharePoint 2010 : Modify the Top or Left Navigation Bar (part 1)
- Windows Server 2008 : Configure the TS RemoteApp Manager
- Windows 7: Troubleshooting Startup - Recovering Using the System Recovery Options
- Use the Microsoft Management Console (MMC)
- SharePoint 2010 : Use the Datasheet View to Add, Edit, or Delete Items and Files
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS