SQL Server Errors Handling Overview

The previous programming habits have never been quite good. Whether it is writing C, C ++, C #, or database scripts, debugging and testing are generally successful, and no exception handling such as try/catch is added. Now, for good programming habits and a case, I began to learn to add Exception Handling Code when it is necessary to handle exceptions.

SQL Server seems to have a poor error handling function in the past, but it must have been greatly improved... Good or bad, learning to use is the first step.

1. Basics
Error Message 1.1

The error message of SQL Server is as follows:
Server: Msg 547, Level 16, State 1, Procedure error_demo_sp, Line 2
UPDATE statement conflicted with column foreign key constraint 'fk7 _ acc_cur '.
The conflict occurred in database 'bos _ sommar ', table 'currencies', column 'curcode '.
The statement has been terminated.

It includes the following parts:
Message number: Custom messages greater than 50000. You can use sp_addmessage to add messages in the master .. sysmessages table.
Severity level: 0-25, 0-10 is a message or warning, and the programming error is 11-16, and the 17-25 is a resource error, hardware error, or an internal SQL Server Error.> = 20 errors may cause connection termination.
State: 0-127, but uninitialized ented... The exception is that for OSQL and ISQL, if the State is 127, it will stop and set ERRORLEVEL to message number to handle errors during script installation.
Line: If it is 0, it indicates an error occurred while calling procedure.

The client determines the rendering format.

Database Engine error handling has two levels: T-SQL and application code.

1.2 T-SQL
1.2.1 TRY... CATCH
The TRY/CATCH usage in the T-SQL is similar to that in C ++ or C #, but it does not look Finally...
Note 1: After an error is processed in the CATCH Block, the first sentence after the end catch is skipped. If end catch is already the last sentence of a stored procedure or trigger, return the sentence that calls the stored procedure or trigger. All codes with errors in the TRY block are ignored.
NOTE 2: severity (severity)> = 20, which causes the database engine to close the connection and cannot be processed.
NOTE 3: severity (severity) <= 10, which is regarded as a warning or information message and cannot be processed
Note 4: To capture errors in the compilation or recompilation phase, use sp_executesql

1.2.2 ERROR Function
To get exception information in the T-SQL, first add TRY... CATCH structure, then use ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY and ERROR_STATE in the structure. Look at the function name and you will know what it is. Note 1: If it is used outside the TRY... CATCH structure, NULL is returned. (In a case, the customer asks why NULL is returned. This is probably the reason ...)
NOTE 2: no matter how many times the CATCH Block is referenced or where it is referenced, the returned information is the same.
Note 3: For nested CATCH blocks, which layer of Exception Processing returns the information of which layer.

1.2.3 @ ERROR
Catch the error number of a previously running T-SQL statement, except for a special case, which is generally 0 by default, for messages above 11 are auto-incrementing. For versions earlier than SQL Server 2005, it is difficult to obtain detailed error information, but it is still possible to implement DBCC OUTPUTBUFFER if you have sysadmin permissions ). It is generally impossible to prevent SQL Server from reporting errors.
The important 11-16 error codes are as follows:
11-Specified Database Object Not Found
12-Unused
13-User Transaction Syntax Error
14-Insufficient Permission
15-Syntax Error in SQL Statements
16-Miscellaneous User Error

Note: 1: @ ERROR is irrelevant to the ERROR that occurs after running.
NOTE 2: If a TRY block ERROR occurs, @ ERROR should be the first sentence of CATCH. If it is not a TRY Block ERROR, @ ERROR should be followed by the ERROR.
NOTE 3: @ ERROR must be saved to a temporary variable to operate on it.

Error:

Code
Create table notnull (a int not null)
DECLARE @ value int
INSERT notnull VALUES (@ value)
IF @ error <> 0
PRINT '@ error is' + ltrim (str (@ error) + '.'
-- @ Error is 0 is returned.

Correct:

Code
Create table notnull (a int not null)
DECLARE @ err int,
@ Value int
INSERT notnull VALUES (@ value)
SELECT @ err =@@ error
IF @ err <> 0
PRINT '@ err is' + ltrim (str (@ err) + '.'
-- Returns @ err is 515.

Note 4: related variables: @ rowcount (rows affected by the error) and @ trancount (number of transactions)

1.2.4 Uncommittable Transactions (uncommitted Transactions)
Transactions in TRY... CATCH may be opened but cannot be committed. This status is returned when you set ddl or SET XACT_ABORT to ON.
The XACT_STATE function is used to test the transaction status in the CATCH Block. XACT_STATE has three return values: 1 is a normal active TRANSACTION, 0 is a non-active user TRANSACTION, and-1 is an active TRANSACTION, but an error exists and cannot be committed, so it must be rolled back by rollback transaction.

1.2.5 returned value of Stored Procedure
If no error occurs, the returned value is 0. If an error occurs, the value-1 or 0 may be returned.

1.2.6 SET XACT_ABORT
SET XACT_ABORT OFF corresponding to Statement-termination
SET XACT_ABORT ON corresponding Batch-abortion
XACT_ABORT should be the abbreviation of execution action aborted.

1.3 Handling Errors and Messages in Applications
The processing methods for ODBC, ADO, ADO. NET, SqlClient, and OleDb are also different.

2. Behind exceptions
Different methods may be used to handle different SQL Server errors.

Possible actions:
1. Statement termination (Statement-termination ). Manual ROLLBACK transaction is required for open TRANSACTION. Otherwise, INSERT, UPDATE, and DELETE operations will not be rolled back automatically.
2. Scope-abortion ). Both A and B are stored procedure, and A calls B and B with an error aborted, but the code after A calls B is executed as usual. It is usually limited to compilation errors.
3. Stop Batch (Batch-abortion ). The entire batch submitted by the client to SQL Server is aborted, and all open transactions are rolled back.
4. Connection-termination ). Open transaction rollback. @ ERROR: the value cannot be obtained. It is usually a serious server error, except for one exception: sp_OAxxxxx calls a custom stored procedure or OLE objects (it should be related to DMO ). RAISERROR> = 20.

It is difficult to determine whether the statement is terminated or the batch is aborted.

The following two cases do not occur (but are not officially described:
1. Transaction rollback, but the current batch continues to be executed
2. the batch is aborted, but the transaction is not rolled back.

One thing to note is the client's batch cancellation (batch-cancellation ). The client can request the SQL Server to abort the execution of batch at any time. the SQL Server may respond immediately or wait for a while to respond, transactions will not be rolled back (rollback will be performed in INSERT/UPDATE/DELETE/Trigger ). A more common scenario is client timeout. ODBC, ole db, ADO, and ADO. Net both have 30 seconds of default timeout settings. You can set. CommandTimeout to avoid this problem.

How does SQL Server decide what actions to take? In addition to the special case SET XACT_ABORT OFF (this is the key issue of a customer. Since SQL Server 2005 says that this feature has been canceled in the trigger, it can also work clearly ...) And Trigger Context (the customer's problem is that SET XACT_ABORT OFF in the Trigger ...)

For Trigger Context, all errors terminate the batch and start rollback from this node (except for RAISERROR and error 266 ). In addition, if @ trancount is 0 (at least 1 for Trigger Context), it is terminated. Therefore, the error handling template in the Trigger should be:

Code
If exists (SELECT *
FROM inserted I
JOIN abainstallhistory inh ON I. inhid = inh. inhid
WHERE inh. ss_label <> I. ss_label
OR inh. ss_label is null and I. ss_label IS NOT NULL
OR inh. ss_label is not null and I. ss_label IS NULL)
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('values on ss_label does not match abainstallhistory. ', 16, 1)
RETURN
END

 

References:
SQL Server BOL-Handling Database Engine Errors
Http://www.sommarskog.se/error-handling-I.html
Http://www.sommarskog.se/error-handling-II.html
Http://rusanu.com/2007/10/31/error-handling-in-service-broker-procedures/
Http://www.windows-tech.info/15/397cd9af617c7a4e.php