SQL SERVER Error: raiserror

Reprinted to http://baike.baidu.com/view/3838799.htm

Return the User-Defined error message and set the system flag to record the error. By using the RAISERROR statement, the client can retrieve entries from the sysmessages table, or dynamically generate a message using the severity and status information specified by the user. After the message is defined, it is returned to the client as a server error message.

Syntax: RAISERROR ({Msg_id | msg_str }{ ,Severity ,State }[ ,Argument [ ,... N] )[WITH option [ ,... N] The msg_id parameter is stored in SysmessagesUser-Defined error information in the table. The error number of the User-Defined error message must be greater than 50,000. Error 50,000 is generated by special messages. Msg_str is a special message. Its format is similar to that of PRINTF in C. This error message can contain a maximum of 400 characters. If the information contains more than 400 characters, only the first 397 characters can be displayed and a ellipsis is added to indicate that the information has been truncated. The Standard Message ID of all specific messages is 14,000. Msg_str supports the following format: % [[flag] [width] [precision] [{h | l}] parameters that can be used in msg_str include: flag is used to determine the spacing and alignment code of user-defined error messages.

Code	Prefix or alignment	Description
-(Minus)	Left aligned	Returns the left-aligned result within the specified field width.
+ (Plus)	+ (Plus) or-(minus) prefix	If the output value is of the signed type, add the plus sign (+) or minus sign (-) before the output value (-).
0 (0)	Zero Filling	If there is 0 before the width, add zero until the minimum width is met. If 0 and-appear, 0 is ignored. If 0 is specified in integer format (I, U, X, X, O, d), 0 is ignored.
# (Number)	Use the 0x prefix for the hexadecimal type of x or X	When the o, x, or X format is used, the # flag adds 0, 0x, or 0X before any non-zero value. This flag is ignored when there is a # mark before d, I, or u.
''(Space)	Space Filling	If the output value is signed and positive, a space is added before the value. This flag is ignored if it is included in the plus sign (+.

Width is an integer that defines the minimum width. The asterisk (*) allows precision to determine the width. Precision is the maximum number of characters output by an output field, or the minimum decimal point output by an integer. The asterisk (*) allows argument to determine the precision. {H | L} type is used together with character types D, I, O, X, X, or U for CreationShort Int(H) orLong Int(L) type value.

Character Type	Indicates
D Or I	Signed integer
O	Unsigned octal number
P	Pointer type
S	String
U	Unsigned integer
X or X	Unsigned hexadecimal number

DescriptionNot SupportedFloat,Double-precision and single-precision character types. Severity User-Defined severity levels associated with messages. You can use a severity level ranging from 0 to 18. The severity levels between 19 and 25 can only beSysadminUsed by fixed server role members. To use a severity level between 19 and 25, You must select the with log option.Note:Severity levels between 20 and 25 are considered fatal. In case of a fatal severity level, the client connection ends after receiving the message and records the error in the error log and Application Log. Any integer from 1 to 127 indicating the error call status. The default value of state is 1. Argument is a parameter used to replace the variables defined in msg_str or replace messages corresponding to msg_id. There can be 0 or more substitution parameters; however, the total number of substitution parameters cannot exceed 20. Each substitution parameter can be a local variable or any of these data types:Int1,Int2,Int4,Char,Varchar,BinaryOrVarbinary. Other data types are not supported. Option is incorrect. Option can be one of the following values:

Value	Description
LOG	Log errors to server error logs and application logs. Currently, the error logs recorded in server error logs are limited to a maximum of 440 bytes.
NOWAIT	Send the message to the client immediately.
SETERROR	Set@@The ERROR value is set to msg_id or 50000, which is irrelevant to the severity level.

Note if you useSysmessagesIf the message is created in the msg_str format shown above, parameters (argument1, argument2, and so on) will be provided to the message of the provided msg_id ). When RAISERROR is used to create and return user-defined error messagesSp_addmessageAdd User-Defined error information, useSp_dropmessageDelete user-defined error messages. When an ERROR occurs, the ERROR code is stored in the @ ERROR function, which stores the latest ERROR code. For messages with a severity level of 1 to 10, @ ERROR is set to 0 by default. Example

A. Create A specific message

The following example shows two possible errors. The first type of error is simple. Static messages are generated. The second type of error is generated dynamically based on the attempt to modify it. Create trigger employee_insupd ON employee for insert, update as/* Get the range of level for this job type from the jobs table. */DECLARE @ MIN_LVL tinyint, @ MAX_LVL tinyint, @ EMP_LVL tinyint, @ JOB_ID smallint SELECT @ MIN_LVl = min_lvl, @ MAX_LV = max_lvl, @ EMP_LVL = I. job_lvl, @ JOB_ID = I. job_id FROM employee e, jobs j, inserted I WHERE e. emp_id = I. emp_id AND I. job_id = j. job_id IF (@ JOB_ID = 1) and (@ EMP_lVl <> 10) begin raiserror ('job id 1 expects the default level of 10. ', 16, 1) rollback transaction end else if not @ EMP_LVL BETWEEN @ MIN_LVL AND @ MAX_LVL) begin raiserror ('the level for job_id: % d shoshould be between % d and % d. ', 16, 1, @ JOB_ID, @ MIN_LVL, @ MAX_LVL) ROLLBACK TRANSACTION END

B. Create a special message in sysmessages

The following example shows how to runEmployee_insupdThe trigger achieves the same effect as the RAISERROR, and The RAISERROR is stored inSysmessagesThe method of passing parameters in the table. The message passesSp_addmessageSystem stored procedure, added to message 50005SysmessagesTable.DescriptionThe following example is for example only. Raiserror (50005, 16, 1, @ job_id, @ min_lvl, @ max_lvl) the error message Microsoft & reg; SQL Server 2000 setsSysmessagesThe messages in the system table are written into the SQL Server Error Log and Microsoft Windows & reg; 2000 or Microsoft Windows NT & reg; 4.0 Application Log, or the messages are sent to the client. The SQL Server can return an error message when a problem occurs, or use the raiserror statement to manually generate an error message. The raiserror statement provides centralized error information management. Raiserror can beSysmessagesYou can use a hard-coded (User-Defined) message to retrieve existing entries from a table. When raiserror returns a user-defined error message, it also sets an error in the system variable record. A message can contain a string in the C printf format, which can be filled by the parameter specified by raiserror at runtime. After the message is defined, it is sent back to the client as a server error message. Whether returned from SQL Server or through raiserror statements, each message contains a message number that uniquely identifies the error message. Indicates the Severity Level of the problem type. The error status number that identifies the source of the error (if the error can be issued from multiple locations ). Declares the message body of the problem (sometimes possible solution. For example, if the accessed table does not exist: the error message sent from select * bogus to the client is similar to the following: SERVER: error message 208, level 16, status 1 Object Name 'bogg' is invalid. QueryMasterIn the databaseSysmessagesTable to view the SQL server error information list. RelatedSysmessagesFor more information, see system error information. The severity level of the error message can be used to indicate the type of the problem Microsoft & reg; SQL Server 2000 encountered. The information with the severity level of 10 is an information message, indicating that the problem is caused by an error when the information is entered. Errors with severity levels 11 to 16 are generated by the user and can be corrected by the user. Critical errors ranging from 17 to 25 indicate software or hardware errors. When a serious error of 17 or higher occurs, notify the system administrator. The system administrator must resolve these errors and track the frequency of errors. When an error with a severity level of 17, 18, or 19 occurs, a specific statement can continue even if it cannot be executed. The system administrator should monitor all problems that can generate severity levels from 17 to 25, print error logs containing information, and find the location where the error occurred. If the problem affects the entire database, you can use dbcc checkdb (database) to determine the degree of damage. DBCC can identify the objects that must be deleted and fix the damage. If the damage range is large, the database must be restored. When a user-defined error message is specified with RAISERROR, the error message number greater than 50,000 and the severity level from 0 to 18 are used. Only the system administrator can issue a RAISERROR with severity levels ranging from 19 to 25. The error message returned when the severity level is 0 to 19 and the severity level is 10 is an information error. The error information generated by the user for severity levels from 11 to 16 can be corrected by the user. Errors with severity levels 17 and 18 are generated by resource or system errors. user sessions are not interrupted. UseSp_addmessageTo add user-defined messages with severity levels from 1 to 25Sysmessages. Only the system administrator can add messages with severity levels from 19 to 25. The system administrator should report error messages with severity levels 17 and higher.Severity Level 10: status informationThis is an information message, indicating that the problem is caused by incorrect user input information. Severity Level 0 cannot be seen in SQL Server.Severity levels 11 to 16These messages indicate that the error can be corrected by the user.Severity 17: insufficient resourcesThese messages indicate that the SQL Server has exhausted resources (such as database locks or disk space) or exceeds the limits set by the system administrator.Severity 18: non-severe internal errors detectedThese messages indicate that some types of internal software problems exist, but the statements are executed and the connection to SQL Server remains unchanged. For example, if an internal error is detected by the SQL Server Query processor during query optimization, a message with a severity of 18 is displayed. Every time a message with a severity level of 18 appears, the system administrator should be informed.Severity 19: An SQL server error occurs in the resource.These messages indicate that they have exceeded the internal nonretriable limit and the current batch is terminated. Critical 19 errors rarely occur. However, once an error occurs, it must be corrected by the system administrator or primary support provider. Every time a message with a severity level of 19 appears, the system administrator should be informed. Severity levels 20 to 25 severity levels from 20 to 25 indicate system problems. This is a serious error, meaning that the process (the program code of the task specified in the completion statement) will not run. The process is frozen before it is stopped, and information about the content is recorded and then terminated. The client connection to SQL Server is closed, and the client may not be able to reconnect according to the problem. An error message with a severity level of 19 or higher will stop the current batch processing. If the error message with a severity level of 20 or higher is considered as a critical error, the customer connection will be terminated. Errors in this range may affect all processes in the database and may indicate that the database or object is corrupt. Error messages with severity levels from 19 to 25 are written into error logs.Severity Level 20: SQL Server severe errors in the current processThese messages indicate that the statement encountered a problem. Because the problem only affects the current process, the database itself is unlikely to be damaged.Severity Level 21: SQL Server severe errors in the database (dbid) ProcessThese messages indicate that all processes in the current database are affected, but the database itself is not likely to be damaged.Severity Level 22: SQL Server serious error table integrity doubtThese messages indicate that the table or index specified in the message is damaged due to software or hardware problems. Critical level 22 errors rarely occur. However, if this error occurs, run dbcc checkdb to check whether other objects in the database are damaged. The problem may only exist in the speeding cache, rather than the disk itself. If so, restarting SQL Server will fix this problem. To continue working, you must connect to SQL Server again. Otherwise, use DBCC to fix the problem. In some cases, it is necessary to restore the database. If the restart is not helpful, the problem exists on the disk. Sometimes, destroying the specified object in the error message can solve this problem. For example, if the message says that SQL Server finds a row with a length of 0 in a non-clustered index, delete the index and recreate it.Severity Level 23: SQL Server severe error: database integrity doubtThese messages indicate that the integrity of the entire database is faulty due to hardware or software issues. Critical Level 23 errors rarely occur. However, if yes, run dbcc checkdb to determine the degree of damage. The problem may only exist in the speeding cache, rather than the disk itself. If so, restarting SQL Server will fix this problem. To continue working, you must connect to SQL Server again. Otherwise, use DBCC to fix the problem. In some cases, it is necessary to restart the database.Severity Level 24: hardware errorThese messages indicate that some types of media failed. The system administrator may have to reload the database. You may also need to call the hardware vendor.Deep Learning:Http://msdn.microsoft.com/zh-cn/library/ms178592 (SQL .105). aspx