SQL Server error severity level and exception handling

For a description of the error severity level of SQL Server, take a strong look at the following two links

Offline Help

Ms-help://ms. Sqlcc.v9/ms. Sqlsvr.v9.zh-chs/sqlerrm9/html/3e7f5925-6edd-42e1-bf17-f7deb03993a7.htm

Online Help

Http://technet.microsoft.com/zh-cn/library/ms164086.aspx

In short, the error severity of SQL Server is divided into three levels

1. Minor error: severity level is 0-10

2. Medium Error: Severity level is 11-19

3. Critical Error: Severity level is 20-25

All the error messages saved by SQL Server can be viewed by retrieving the sys.messages view, as follows

Why do we need to say this? is because we need to know that the behavior caused by different error severity levels is not the same

1. Minor error: severity level 0-10 = = "The client program is not sent an error message and continues to work by default. Which means it can't be caught in a catch.

2. Medium Error: Severity level 11-19 = = "can be caught (either in T-SQL or in the client program)

3. Fatal error: Severity level 20-25 = = "SQL Server will force the connection to be turned off." Obviously, it's not going to catch up.

about using try in T-SQL ... Catch handling exception, please refer to the link below

Http://technet.microsoft.com/zh-cn/library/ms179296.aspx

There are two cases of handling exceptions in the client program

1. For error severity level between 11-19, can be try ... Catch to the SqlException type, which can then be processed

Http://msdn.microsoft.com/zh-cn/library/system.data.sqlclient.sqlexception (vs.80). aspx

2. For a system error with error severity level 0-10, or a message sent through the print statement, or an error from the RAISERROR statement, you should write the InfoMessage event for the connection object if it needs to be handled inside the client program

Http://msdn.microsoft.com/zh-tw/library/a0hee08w.aspx

As for the InfoMessage event, because it can accept output messages from the print statement, some friends use it to track the progress of the stored procedure, and so on, there is an example

Http://www.cnblogs.com/hackzai/archive/2005/04/07/133635.html

There is one more interesting property, Fireinfomessageeventonusererrors. This property is true, so in addition to 0-10 will be handled by the event, 11-19 can also be handled by the event, and do not need to use the try ... Catch

Http://msdn.microsoft.com/zh-tw/library/system.data.sqlclient.sqlconnection.fireinfomessageeventonusererrors.aspx

Here is an example

private void Bttest_click (object sender, EventArgs e)
{
using (SqlConnection conn = new SqlConnection (system.configuration.configurationmanager.connectionstrings[" Northwind "]. ConnectionString))
{
Conn. Fireinfomessageeventonusererrors = true;
Conn.infomessage + = new Sqlinfomessageeventhandler (conn_infomessage);
SqlCommand cmd = conn. CreateCommand ();
Cmd.commandtext = "DROP TABLE TABLE1"; Here I deliberately let it go wrong, because the table does not exist. Because Fireinfomessageeventonusererrors is set to true earlier, it is automatically handled by the InfoMessage event
Conn. Open ();
Cmd. ExecuteNonQuery ();
Cmd.commandtext = "RAISERROR (' This is the message from the RAISERROR statement ', 10, 1)";
Cmd. ExecuteNonQuery ();
Conn. Close ();
}

}

void Conn_infomessage (object sender, Sqlinfomessageeventargs e)
{
MessageBox.Show (String. Format ("Source:{0},message:{1}", E.source, E.message));
}

It is worth noting that both SqlException and InfoMessage event arguments Sqlinfomessageeventargs contain a collection of errors that contain all the error instances. A perfect exception handler that should traverse them. such as the following

public static void Showsqlexception (String connectionString)
{
String queryString = "EXECUTE nonexistantstoredprocedure";
StringBuilder errormessages = new StringBuilder ();
using (SqlConnection connection = new SqlConnection (connectionString))
{
SqlCommand command = new SqlCommand (queryString, connection);
Try
{
Command. Connection.Open ();
Command. ExecuteNonQuery ();
}
catch (SqlException ex)
{
for (int i = 0; i < ex. Errors.Count; i++)
{
Errormessages.append ("Index #" + i + "\ n" +
"Message:" + ex. Errors[i]. Message + "\ n" +
"LineNumber:" + ex. Errors[i]. LineNumber + "\ n" +
"Source:" + ex. Errors[i]. Source + "\ n" +
"Procedure:" + ex. Errors[i]. Procedure + "\ n");
}
Console.WriteLine (Errormessages.tostring ());
}
}
}

SQL Server error severity level and exception handling