domingo, 24 de febrero de 2008

Interceptando Errores en SQL Server con .Net Framework 2.0

Una conexión SqlConnection permanecerá abierta mientras el nivel de severidad del error sea de 19 o menos, para errores iguales o por encima de un nivel 20 se cerrará automáticamente la conexión.
A continuación se muestra información del nivel de severidad y una descripción (información extraída de los BOL de SQL Server 2005)

0-9
Informational messages that return status information or report errors that are not severe. The Database Engine does not raise system errors with severities of 0 through 9.
10
Informational messages that return status information or report errors that are not severe. For compatibility reasons, the Database Engine converts severity 10 to severity 0 before returning the error information to the calling application.
11-16
Indicate errors that can be corrected by the user.
11
Indicates that the given object or entity does not exist.
12
A special severity for queries that do not use locking because of special query hints. In some cases, read operations performed by these statements could result in inconsistent data, since locks are not taken to guarantee consistency.
13
Indicates transaction deadlock errors.
14
Indicates security-related errors, such as permission denied.
15
Indicates syntax errors in the Transact-SQL command.
16
Indicates general errors that can be corrected by the user.
17-19
Indicate software errors that cannot be corrected by the user. Inform your system administrator of the problem.
17
Indicates that the statement caused SQL Server to run out of resources (such as memory, locks, or disk space for the database) or to exceed some limit set by the system administrator.
18
Indicates a problem in the Database Engine software, but the statement completes execution, and the connection to the instance of the Database Engine is maintained. The system administrator should be informed every time a message with a severity level of 18 occurs.
19
Indicates that a nonconfigurable Database Engine limit has been exceeded and the current batch process has been terminated. Error messages with a severity level of 19 or higher stop the execution of the current batch. Severity level 19 errors are rare and must be corrected by the system administrator or your primary support provider. Contact your system administrator when a message with a severity level 19 is raised. Error messages with a severity level from 19 through 25 are written to the error log.
20-25
Indicate system problems and are fatal errors, which means that the Database Engine task that is executing a statement or batch is no longer running. The task records information about what occurred and then terminates. In most cases, the application connection to the instance of the Database Engine may also terminate. If this happens, depending on the problem, the application might not be able to reconnect.
Error messages in this range can affect all of the processes accessing data in the same database and may indicate that a database or object is damaged. Error messages with a severity level from 19 through 25 are written to the error log.
20
Indicates that a statement has encountered a problem. Because the problem has affected only the current task, it is unlikely that the database itself has been damaged.
21
Indicates that a problem has been encountered that affects all tasks in the current database, but it is unlikely that the database itself has been damaged.
22
Indicates that the table or index specified in the message has been damaged by a software or hardware problem.
Severity level 22 errors occur rarely. If one occurs, run DBCC CHECKDB to determine whether other objects in the database are also damaged. The problem might be in the buffer cache only and not on the disk itself. If so, restarting the instance of the Database Engine corrects the problem. To continue working, you must reconnect to the instance of the Database Engine; otherwise, use DBCC to repair the problem. In some cases, you may have to restore the database.
If restarting the instance of the Database Engine does not correct the problem, then the problem is on the disk. Sometimes destroying the object specified in the error message can solve the problem. For example, if the message reports that the instance of the Database Engine has found a row with a length of 0 in a nonclustered index, delete the index and rebuild it.
23
Indicates that the integrity of the entire database is in question because of a hardware or software problem.
Severity level 23 errors occur rarely. If one occurs, run DBCC CHECKDB to determine the extent of the damage. The problem might be in the cache only and not on the disk itself. If so, restarting the instance of the Database Engine corrects the problem. To continue working, you must reconnect to the instance of the Database Engine; otherwise, use DBCC to repair the problem. In some cases, you may have to restore the database.
24
Indicates a media failure. The system administrator may have to restore the database. You may also have to call your hardware vendor.

Ahora veamos la manera de interceptar los errores desde nuestra aplicación, el código que muestro a continuación intercepta posibles errores a la hora de conectar con nuestra base de datos

Private Sub ConectarBD(ByVal cadenaConexion As String)
Dim conn As New SqlConnection(cadenaConexion)
Try
conn.Open()
Catch ex As SqlException
Dim errorMessage As String = ""
'Recorremos todos los errores que nos devuelva.
'Podemos inspeccionar los errores para devolver mensajes personalizados dependiendo
'del tipo de error.
For Each connectionError As SqlError In ex.Errors
errorMessage += connectionError.Message & "error: " & _
connectionError.Number.ToString & ")" & Environment.NewLine
'Aquí podríamos poner un SELECT CASE con los códigos de error más comunes, como
'login de usuario, nombre de la base de datos, etc
If connectionError.Number = 18456 Then
MsgBox("Login inválido, por favor introduzca unas credenciales válidas")
End If
Next
MessageBox.Show(errorMessage)
Finally
conn.Close()
End Try
End Sub

3 comentarios:

Alex dijo...

At work with sql files I advise to use-sql *mdf recovery,because software has many other capabilities and has free status to the best of my memory,program can help with this problem and retrieve the data, that was considered to be lost,mwill extract housekeeping data from the source database and preview the data, that can be recovered,this tool is a good solution to recover data from corrupted databases in MS SQL Server format,restore databases represent files, like any other documents, they can be easily corrupted by viruses, all sorts of malware, hard drive failures, file system errors, incorrect user actions, etc,supports both data extraction to your hard drive as scripts in SQL format and data export directly to a database in MS SQL Server format.

Alexis dijo...

For work with sql files advise to use-repair sql mdf,because this tool helped me many times and it is free as far as I know.Moreover program can too easily open damaged files of mdf (main data file) and ndf (secondary data file) format.

Francisco Ruiz dijo...

Spam puro y duro? Mola.
Y por cierto, porqué gastarme pasta en un programa si se que puedo reparar la base de datos??? o extraer los datos y pasarlos a otra BD. En fin amigo bot Alex, Alexis (no se porqué me da que sois el mismo bot xDDD) que te vaya bonito.
See you on hell.