Gestión de Errores y Transacciones en SQL Server

Gestión de Errores y Transacciones en SQL Server

Gestión de Errores y Transacciones en SQL Server

Autor: Erland Sommarskog                                   Traducción al Español: Geovanny Hernández

Primera Parte – Punto de arranque en la gestión de errores

Introducción

Este artículo es el primero de una serie de tres acerca de la gestión de errores y transacciones en SQL Server.  El objetivo de este primer artículo es dar un punto de arranque en la gestión de errores mostrando un patrón básico el cual es bueno para el eje central de tu código.  Esta parte está escrita con el lector inocente e inexperto en mente, por esto he omitido intencionalmente muchos detalles.  El propósito aquí es decirte como sin detenerse mucho en porque.  Si tomas mis palabras como verdad, quizás prefieras solo leer esta parte y ahorrarte las otras dos partes para un momento posterior en tu carrera.

Por otro lado, si me preguntas mis lineamientos, ciertamente necesitas leer las otras dos partes , en donde entro en mayor profundidad en la exploración de detalles del muy confuso mundo de la gestión de errores y transacciones en SQL Server.  Las partes dos y tres, así también como los tres apéndices, están enfocados hacia los lectores con una mayor experiencia general en programación, aunque no necesariamente con SQL Server.  Este primer artículo es corto, siendo las partes dos y tres considerablemente más largos.

Indice de todos los artículos relacionados con la gestión de Errores

A continuación una lista de todos los artículos en esta serie:

Part One – Punto de partida para la gestión de Errores (este artículo).

Part Two – Commands and Mechanisms.

Part Three – Implementation.

Appendix 1 – Linked Servers. (Extends Part Two.)

Appendix 2 – CLR. (Extends both Parts Two and Three.)

Appendix 3 – Service Broker. (Extends Part Three.)

Porque la Gestión de Errores?

Porque debemos tener un manejador de errores en nuestro código? Hay muchas razones.  En una aplicación de formulario nosotros validamos las entradas de los usuarios y reportamos al usuario de sus equivocaciones.  Estos errores del usuario son errores anticipados.  Pero nosotros además debemos gestionar los errores no anticipados.  Esto es, errores que ocurren porque nosotros hemos pasado por alto algo cuando escribimos nuestro código.  Una estrategia simple es abortar la ejecución o al menos revertirla a un punto donde conocemos que tenemos el control total.  Cualquier cosa que hagamos,  simplemente ignorar un error no anticipado es algo que no deberíamos permitirnos nunca.  Esto puede tener graves consecuencias y causar que la aplicación presente información incorrecta al usuario o incluso peor al permitir la persistencia de los datos en la base de datos.  Es además importante comunicar que un error ha ocurrido, es decir evitar que el usuario piense que la operación fue bien, cuando el código de hecho no realizo absolutamente nada.

En un sistema de base de datos, usualmente queremos que las actualizaciones sean atómicas.  Por ejemplo, decimos que la tarea es transferir dinero de una cuenta a otra.  Para este fin, necesitamos actualizar  dos filas en la tabla CashHoldings y agregar dos filas a la tabla Transactions.  Es absolutamente impermisible que un error o una interrupción resultasen en dinero que está siendo depositado dentro de la cuenta beneficiaria sin haber realizado el retiro de la otra cuenta.  Por esta razón, en una aplicación de base de datos, la gestión de errores además incluye la gestión de transacciones.  En este ejemplo, necesitamos envolver la operación en la sentencia BEGIN TRANSACTION y COMMIT TRANSACTION, pero no solo eso: en caso de un error, debemos asegurarnos que la transacción se revierta.

Comandos Esenciales

Iniciaremos viendo los comandos más importantes que necesitaremos para la gestión de errores.  En la parte dos cubriré todos los comandos relacionados con la gestión de errores y transacciones.

TRY-CATCH

El principal vehículo para la gestión de errores es TRY-CATCH, el cual nos recuerda a construcciones similares en otros lenguajes.  La estructura es:

BEGIN TRY
<regular code>
END TRY
BEGIN CATCH
<error handling>
END CATCH

Si cualquier error ocurre en el bloque <regular code>, la ejecución es transferida al bloque CATCH, y el manejo de error es ejecutado.  Típicamente, tu sección CATCH va a deshacer cualquier transacción abierta y subirá el error, por lo que la aplicación cliente que lo ha invocado entenderá que algo salió mal.  Así como el disparador del error, lo veremos posteriormente en este artículo.

Aquí un ejemplo rápido:

BEGIN TRY
   DECLARE @x int
   SELECT @x = 1/0
   PRINT 'Not reached'
END TRY
BEGIN CATCH 
   PRINT 'This is the error: ' + error_message()
END CATCH

La salida es:

This is the error: Divide by zero error encountered.

Retornaremos después a la función error_message. Vale la pena mencionar que el uso de PRINT en tu manejador CATCH es algo que se debería hacer únicamente cuando estemos experimentando.  Por lo que no deberías hacerlo en el código de una aplicación real.

Si <regular code> llama a procedimientos almacenados o invoca triggers, cualquier error que ocurra en ellos transferirá su ejecución al bloque CATCH.  Específicamente, cuando un error ocurre, SQL Server buscara hasta encontrar el manejador CATCH, y si no hay ninguno, SQL Server enviara el mensaje de error al cliente.

Hay una limitación muy importante con TRY-CATCH que necesitas tener en cuenta:  No se capturan errores de compilación que ocurren en el mismo ámbito.  Considere:

CREATE PROCEDURE inner_sp AS
   BEGIN TRY
      PRINT 'This prints'
      SELECT * FROM NoSuchTable
      PRINT 'This does not print'
   END TRY
   BEGIN CATCH
      PRINT 'And nor does this print'
   END CATCH
go
EXEC inner_sp

La salida es:

This prints

Msg 208, Level 16, State 1, Procedure inner_sp, Line 4

Invalid object name ‘NoSuchTable’.

Como has visto se ingresa al bloque TRY, pero cuando el error ocurre, la ejecución no es transferida al bloque CATCH como se esperaba.  Esto es cierto para todos los errores de compilación tales como columnas no encontradas, alias incorrectos, etc.; que ocurren en tiempo de ejecución. Errores de compilación pueden ocurren en tiempo de ejecución en SQL Server debido a la resolución de nombres en diferido, una (mala) característica donde SQL Server te permite crear un procedimiento que se refiere a tablas no existentes.

Hay errores que no son completamente capturables, tú no puedes capturarlos en el ámbito en que ellos ocurren, pero puedes capturarlos en ámbitos externos.  Añadir este código para el siguiente ejemplo:

CREATE PROCEDURE outer_sp AS
   BEGIN TRY
      EXEC inner_sp
   END TRY
   BEGIN CATCH
      PRINT 'The error message is: ' + error_message()
   END CATCH
go
EXEC outer_sp

Ahora obtendremos esta salida:

This prints

The error message is: Invalid object name ‘NoSuchTable’.

En esta ocasión el error es capturado porque hay un manejador CATCH externo.

SET XACT_ABORT ON

Tus procedimientos almacenados deberían siempre incluir esta sentencia al inicio:

SET XACT_ABORT, NOCOUNT ON

Este activa dos opciones de sesión que están desactivados por default debido a razones de tipo legacy, pero la experiencia ha probado que la mejor práctica es siempre activarlas.  La conducta por defecto en SQL Server cuando no está envuelto en un TRY-CATCH es que algunos errores abortan la ejecución y revierten cualquier transacción abierta, mientras que otros errores continúan la ejecución en la siguiente sentencia. Cuando activas XACT_ABORT ON, casi todos los errores tienen el mismo efecto: cualquier transacción abierta se deshace y su ejecución es abortada.  Hay unas pocas excepciones de las cuales la más prominente es la sentencia RAISERROR.

La opción XACT_ABORT es esencialmente más confiable para la gestión de errores y transacciones.  Particularmente, con el comportamiento por defecto donde hay importantes situaciones en que la ejecución puede ser abortada sin que ninguna transacción abierta se revierta, incluso si tienes un TRY-CATCH.  Hemos visto un ejemplo en la sección anterior donde aprendimos que TRY-CATCH no captura los errores de compilación en el mismo ámbito.  Una transacción abierta la cuál no se revierte en caso de un error puede causar grandes problemas si la aplicación sigue adelante sin realizar la aceptación (Commit) o haber revertido(Rollback).

Para la buena gestión de errores en SQL Server, necesitas tanto TRY-CATCH y SET XACT_ABORT ON.  De estos dos, SET XACT_ABORT ON es el más importante.  A nivel de código para entornos de producción no es realmente suficiente confiar en XACT_ABORT, pero para cosas rápidas y simples este puede hacerlo.

La opción NOCOUNT no tiene nada que ver con la gestión de errores, pero lo he incluido a fin de mostrar la mejor práctica.  El efecto de NOCOUNT es que este suprime mensajes como  (1 row(s) affected) que puedes ver en la cejilla Message de SQL Server Management Studio.  Mientras estos contadores de filas pueden ser útiles cuando trabajas interactivamente en SSMM, ellos pueden degradar el rendimiento en una aplicación porque incrementa el tráfico de red.  El contador de filas puede además confundir a aplicaciones cliente mal escritas que pueden llegar a pensar que son conjunto de datos reales.

Anteriormente he usado una sintaxis poco común.  La mayoría de las personas probablemente escriben dos sentencias separadas:

SET NOCOUNT ON
SET XACT_ABORT ON

No hay diferencia entre este y el anterior.  Yo prefiero la versión con un SET y una coma ya que reduce la cantidad de ruido en el código.  Como estas sentencias deberían aparecer en todos tus procedimientos almacenados, ellos deberían disponer del menor espacio posible.

Patrón general para la gestión de Errores

Habiendo visto TRY-CATCH y SET XACT_ABORT ON, vamos a poner las piezas juntas para un patrón que podremos usar en todos nuestros procedimientos almacenados.  Para tomar las cosas con calma, lo primero que mostrare es un ejemplo donde relanzare el error en una forma muy simple, y en la siguiente sección mostrare mejores soluciones.

Para el ejemplo usaremos esta sencilla tabla:

CREATE TABLE sometable(a int NOT NULL,
                       b int NOT NULL,
                       CONSTRAINT pk_sometable PRIMARY KEY(a, b))

Aquí es un procedimiento almacenado que te maestro como deberías trabajar con errores y transacciones.

CREATE PROCEDURE insert_data @a int, @b int AS 
   SET XACT_ABORT, NOCOUNT ON
   BEGIN TRY
      BEGIN TRANSACTION
      INSERT sometable(a, b) VALUES (@a, @b)
      INSERT sometable(a, b) VALUES (@b, @a)
      COMMIT TRANSACTION
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()  
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH

La primera línea en el procedimiento active XACT_ABORT y NOCOUNT en una única sentencia como mostré anteriormente.  Esta línea es la única línea que viene antes de BEGIN TRY. Todo lo demás en el procedimiento debería venir después de BEGIN TRY: declaración de variables, creación de tablas temporales, variables de tipo tabla, y todo lo demás.  Incluso si tienes otros comandos de tipo SET en el procedimiento (rara vez hay una razón para esto), ellos deberían venir después del BEGIN TRY.

La razón por la que yo prefiero tener SET XACT_ABORT, NOCOUNT ON antes de BEGIN TRY es que veo esto como una línea ruidosa, por lo que debería siempre estar allí, para no estresar mis ojos. Esto es realmente un tema de preferencia, y si tú prefieres poner los comandos de tipo SET después de BEGIN TRY, eso estaría bien.   Lo que es importante es que nunca debes poner todo lo demás antes de BEGIN TRY.

La parte entre BEGIN TRY y END TRY es la sustancia principal del procedimiento.  Porque quería incluir una transacción definida por el usuario, introduje una regla de negocio bastante artificiosa que dice que al insertar un par, se debe insertar también el par inverso.  Las dos sentencias INSERT están dentro de BEGIN y COMMIT TRANSACTION.  En muchos casos deberás tener algunas líneas entre BEGIN TRY y BEGIN TRANSACTION.  Algunas veces tendrás además código entre COMMIT TRANSACTION y END TRY, a pesar de que es típico solo una sentencia SELECT hasta el final para retornar datos o asignar valores a los parámetros de salida.  Si tus procedimientos no ejecutan ninguna actualización o solo tiene una única sentencia INSERT/UPDATE/DELETE/MERGE, típicamente no tienes una transacción explicita en absoluto.

Considerando que el bloque TRY podría verse diferente de procedimiento a procedimiento, lo mismo no es cierto para el bloque CATCH.  Tus bloques CATCH deberían ser más o menos tema de copiar y pegar.  Esto es asentarse en algo corto y sencillo para luego utilizarlo sobre el lugar sin pensarlo mucho.  El manejador CATCH anterior  ejecuta tres acciones:

  1. Deshace cualquier transacción abierta.
  2. Relanza el error
  3. Se asegura que el valor de retorno del procedimiento almacenado no sea cero.

Estas acciones deberían siempre estar allí.  Siempre. Es posible argumentar que la línea

IF @@trancount > 0 ROLLBACK TRANSACTION

No es necesaria si no hay transacciones explicitas en el procedimiento, pero nada podría ser más erróneo.  Quizás llames a un procedimiento almacenado el cual inicie una transacción, pero el cual no este habilitado para deshacer la transacción debido a las limitaciones de TRY-CATCH.  Quizás tu o alguien más agregue una transacción explicita al procedimiento en dos años a partir de ahora.  Recordarías agregar esa línea para realizar el roll back entonces?  No deberías contar con ello.  También puedo oír a los lectores que se oponen si el invocador inicio la transacción no deberíamos revertirla… Si, deberíamos, y si quieres conocer por qué necesitas leer la parte dos y tres.  Siempre deshaciendo la transacción en el manejador CATCH es un imperativo categórico para el que sabemos no hay excepciones.

El código para relanzar el error incluye esta línea:

DECLARE @msg nvarchar(2048) = error_message()

La función incorporada error_message() retorna el texto para el error que fue lanzado.  En la siguiente línea, el error es relanzado con la sentencia RAISERROR.  Esta es una forma poco sofisticada para hacerlo, pero hace el trabajo. Veremos alternativas en el siguiente capítulo.

Note: La sintaxis para asignar a las variables un valor inicial  con DECLARE fue introducida en SQL 2008.  Si estas en SQL 2005, necesitaras dividir la línea en un DECLARE y una sentencia SELECT

Siempre relanzarlo? Que si solo quieres actualizar una fila en una tabla con el mensaje de error? Si, esa es una situación que ocurre ocasionalmente, a pesar de que deberías normalmente hacer eso en un bloque interno CATCH el cual es parte de un ciclo.  Tengo un largo ejemplo demostrando esto en la parte tres.  El bloque externo CATCH en un procedimiento es exactamente para capturar y relanzar errores inesperados que no hayas previsto.  Tirar esos errores al suelo es un pecado capital.  Ellos deben ser relanzados.

La sentencia final RETURN es una garantía.  Llamar nuevamente a la sentencia RAISERROR nunca aborta la ejecución, por lo que la ejecución continuara en la siguiente sentencia.  Así como todos los procedimientos son ejecutados usando TRY-CATCH y del mismo modo todo el código cliente está utilizando el manejo de esta excepción no es motivo de preocupación.  Pero tus procedimientos quizás llamados desde código legacy que fue escrito antes de SQL 2005 y la introducción de TRY-CATCH.  En esos días, lo mejor que se podía hacer era ver y retornar valores.  Lo que retorne en realidad no importa, siempre y cuando sea un valor diferente de cero. (Zero es usualmente entendido como exitoso).

La última sentencia en el procedimiento es END CATCH.  Nunca deberías tener ningún código después de END CATCH para la sección externa de TRY-CATCH de tus procedimientos.  Por un lado, nadie que este leyendo el procedimiento nunca vera esa pieza de código.

Habiendo leído toda la teoría.  Permítanme intentar con un caso de prueba:

EXEC insert_data 9, NULL

La salida es:

Msg 50000, Level 16, State 1, Procedure insert_data, Line 12

Cannot insert the value NULL into column ‘b’, table ‘tempdb.dbo.sometable’; column does not allow nulls. INSERT fails.

Permítanme agregar un procedimiento externo para ver qué pasa cuando un error es relanzado repetidamente:

CREATE PROCEDURE outer_sp @a int, @b int AS
   SET XACT_ABORT, NOCOUNT ON
   BEGIN TRY
      EXEC insert_data @a, @b
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH
go
EXEC outer_sp 8, 8

La salida es:

Msg 50000, Level 16, State 1, Procedure outer_sp, Line 9

Violation of PRIMARY KEY constraint ‘pk_sometable’. Cannot insert duplicate key in object ‘dbo.sometable’. The duplicate key value is (8, 8).

Obtenemos el correcto mensaje de error, pero si ves de cerca las cabeceras de este mensaje y los previos, quizás notes un problema:

Msg 50000, Level 16, State 1, Procedure insert_data, Line 12

Msg 50000, Level 16, State 1, Procedure outer_sp, Line 9

Los mensajes de error dan la localización de la sentencia final RAISERROR que fue ejecutada.  En el primer caso, solo el número de la línea es erróneo.  En el segundo caso, el nombre del procedimiento es incorrecto también.  Para procedimientos simples como nuestros procedimientos de prueba, esto no es un gran problema, pero si tienes importantes capas de procedimientos almacenados enlazados, solo teniendo un mensaje de error pero sin conocer donde ocurren haciendo la resolución de problemas mucho más difícil.  Por esta razón, es deseable relanzar el error en una forma en que puedas localizar rápidamente la pieza de código que está fallando, y esto es lo que veremos en el siguiente capítulo.

Tres formas de relanzar el Error

Usando error_handler_sp

Hemos visto error_message(), el cual retorna el texto para un mensaje de error.  Un mensaje de error consiste de importantes componentes, y hay una función error_xxx() para cada una de ellas.  Podemos usar esto para relanzar un mensaje completo que conversa toda la información original, aunque con un formato diferente.  Haciendo esto en cada manejador CATCH sería un grave pecado de duplicación de código, y no hay razón para ello.  No tienes que tener en el bloque CATCH llamadas a error_message() & co, pero ellos retornaran exactamente la misma información si ellos son invocados desde un procedimiento almacenado que captures en llamadas al bloque CATCH.

Permítanme hacer una introducción a error_handler_sp:

CREATE PROCEDURE error_handler_sp AS
 
   DECLARE @errmsg   nvarchar(2048),
           @severity tinyint,
           @state    tinyint,
           @errno    int,
           @proc     sysname,
           @lineno   int
           
   SELECT @errmsg = error_message(), @severity = error_severity(),
          @state  = error_state(), @errno = error_number(),
          @proc   = error_procedure(), @lineno = error_line()
       
   IF @errmsg NOT LIKE '***%'
   BEGIN
      SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') + 
                       ', Line ' + ltrim(str(@lineno)) + '. Errno ' + 
                       ltrim(str(@errno)) + ': ' + @errmsg
   END
   RAISERROR('%s', @severity, @state, @errmsg)

Lo primero que error_handler_sp hace es capturar el valor de todas las error_xxx() funciones dentro de variables locales.  Exactamente lo que eso significa, es algo que no estoy cubriendo en este artículo introductorio, pero que dejado eso para la parte dos.  Regresaremos a la sentencia IF en un segundo.  En su lugar vamos a dar un primer vistazo a la sentencia SELECT desde adentro:

SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') + 
                 ', Line ' + ltrim(str(@lineno)) + '. Errno ' + 
                 ltrim(str(@errno)) + ': ' + @errmsg

El propósito de esta sentencia SELECT es para dar un formato al mensaje de error que pasamos a RAISERROR, y el cual incluye toda la información en el mensaje de error original el cual no podemos inyectarle directamente dentro de RAISERROR.  Necesitamos dar un tratamiento especial al nombre del procedimiento, desde que puede ser NULL para errores que ocurren en lotes ad-hoc o en SQL Dinámico.  De ahí el uso de la función coalesce().  Si no entiendes realmente la forma de la sentencia RAISERROR, discuto este con más detalle en la parte dos.

El mensaje de error formateado inicia con tres asteriscos.  Esto sirve a dos propósitos: 1) Podemos ver directamente que este es un mensaje relanzado desde un manejador CATCH. 2) Este hace posible para error_handler_sp filtrar hacia afuera errores que han sido relanzados una o más veces con la condiciones NOT LIKE ‘***%’ para prevenir que los mensajes de errores se modifiquen una segunda vez.

Así es como el manejador CATCH debería verse cuando usas error_handler_sp:

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC error_handler_sp
   RETURN 55555
END CATCH

Permítanme intentar con algunos casos de prueba:

EXEC insert_data 8, NULL
EXEC outer_sp 8, 8

Este resulta en :

Msg 50000, Level 16, State 2, Procedure error_handler_sp, Line 20

*** [insert_data], Line 5. Errno 515: Cannot insert the value NULL into column ‘b’, table ‘tempdb.dbo.sometable’; column does not allow nulls. INSERT fails.

Msg 50000, Level 14, State 1, Procedure error_handler_sp, Line 20

*** [insert_data], Line 6. Errno 2627: Violation of PRIMARY KEY constraint ‘pk_sometable’. Cannot insert duplicate key in object ‘dbo.sometable’. The duplicate key value is (8, 8).

La cabecera de los mensajes dicen que error ocurrió en error_handler_sp, pero los textos de los mensajes de error nos dan la localización original, tanto el nombre del procedimiento como el número de línea.

Presentare dos métodos más para relanzar errores.  Sin embargo, error_handler_sp es mi principal recomendación para los lectores quienes solo lean esta primera parte.  Es simple y trabajo sobre todas las versiones de SQL Server desde SQL 2005 en adelante.  Hay realmente solo un inconveniente: en algunas situaciones SQL Server lanza dos mensajes de errores, pero las funciones error_xxx() retornan únicamente información acerca de uno de ellos, porque uno de los mensajes de errores es perdido.  Esto puede ser seguramente una dificultad con comandos administrativos como BACKUP/RESTORE, pero es raramente un problema puro del código de la aplicación.

Using ;THROW

En SQL 2012, Microsoft introdujo la sentencia ;THROW para hacer más fácil relanzar errores.  Desafortunadamente, Microsoft hizo un serio error de diseño con este comando y que introdujo una peligrosa trampa.

Con ; THROW no necesitas de ningún procedimiento almacenado que te ayude.  Tu manejador CATACH se convierte en algo tan simple como:

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   ;THROW
   RETURN 55555
END CATCH

La bueno con; THROW es que esta relanza el mensaje de error exactamente como el mensaje original.  Si hubieran dos mensajes originales, ambos serán relanzados lo cual lo hace incluso mejor. Así con todos los otros errores, los errores relanzados por ;THROW pueden ser capturados y relanzados en un manejador externo CATCH.  Si no hay un manejador externo CATCH, la ejecución es abortada, por lo que la sentencia RETURN es de hecho superflua.  Aun así recomiendo que lo mantengas, en caso que cambies de opinión luego sobre ; THROW.

Si tienes SQL 2012 o posterior, cambia la definición de insert_data y outer_sp, e intenta con los casos de prueba nuevamente.  La salida esta vez:

Msg 515, Level 16, State 2, Procedure insert_data, Line 5

Cannot insert the value NULL into column ‘b’, table ‘tempdb.dbo.sometable’; column does not allow nulls. INSERT fails.

Msg 2627, Level 14, State 1, Procedure insert_data, Line 6

Violation of PRIMARY KEY constraint ‘pk_sometable’. Cannot insert duplicate key in object ‘dbo.sometable’. The duplicate key value is (8, 8).

El nombre del procedimiento y el número de línea son fiables y no hay otro nombre de procedimiento que nos confunda.  Además, los números originales de error son conservados.

Hasta este punto podrías estar diciéndote a ti mismo: el debe estar estirando  mis piernas, Microsoft realmente llama el commando ;THROW? No es solo THROW? Cierto, si revisas en los Books Online, no hay al inicio un punto y coma(;).  Pero el punto y coma (;) debe estar ahí.  Oficialmente, es un finalizador de la sentencia previa, pero este es opcional, y de lejos que todos usan punto y coma para finalizar sus sentencias T-SQL.  Lo más importante, si omites el punto y coma antes de THROW esto no resultara en un error de sintaxis, pero en una conducta en tiempo de ejecución la cual es misteriosa para los no iniciados.  Si hay una transacción activo obtendrás un mensaje de error – pero completamente diferente del original.  Incluso peor, si no hay una transacción activo, el error será silenciosamente tirado al suelo.  Algo como omitir por error un punto y coma (;) no debería tener consecuencias tan absurdas.  Para reducir el riesgo para este accidente, siempre piense en el comando como ; THROW.

No se debería de negar que ;THROW tiene sus puntos, pero el punto y como no es la única trampa con este comando.  Si quieres usarlo, te animo a leer el final de la parte dos en esta serie, donde cubro con más detalles sobre ;THROW.  Hasta entonces, adhiérete a  error_handler_sp.

Using SqlEventLog

La tercera forma para relanzar un error es usar SQLEventLog, el cual es una facilidad que presento en gran detalle en la tercera parte.  Aquí solo te daré un rompecabezas.

SqlEventLog ofrece un procedimiento almacenado slog.catchhandler_sp que trabaja de manera similar a error_handler_sp: usa las funciones error_xxx() para recolectar información y relanzar el mensaje de error preservando toda la información acerca de el.  En adición, este loguea el error a la tabla slog.sqleventlog.  Dependiendo del tipo de aplicación que tengas, como una tabla podría ser un gran activo.

Para usar SqlEventLog, tu manejador CATCH debería verse así:

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC slog.catchhandler_sp @@procid
   RETURN 55555
END CATCH

@@procid retorna el identificado del objeto del procedimiento almacenado actual, algo que SqlEventLog usa cuando escribe el log de información a la tabla.  Usando los mismos casos de prueba, esta es la salida con catchhandler_sp:

Msg 50000, Level 16, State 2, Procedure catchhandler_sp, Line 125

{515} Procedure insert_data, Line 5

Cannot insert the value NULL into column ‘b’, table ‘tempdb.dbo.sometable’; column does not allow nulls. INSERT fails.

Msg 50000, Level 14, State 1, Procedure catchhandler_sp, Line 125

{2627} Procedure insert_data, Line 6

Violation of PRIMARY KEY constraint ‘pk_sometable’. Cannot insert duplicate key in object ‘dbo.sometable’. The duplicate key value is (8, 8).

Como ves, los mensajes de error provenientes de SqlEventLog son formateados un poco diferente de los provenientes de error_handler_sp, pero la idea es la misma.  Aquí está un ejemplo de lo que esta logueado en la tabla slog.sqleventlog:

logid logdate                 errno  severity logproc     linenum msgtext

—– ———————– —— ——– ———– ——- —————–

1     2015-01-25 22:40:24.393 515    16       insert_data 5       Cannot insert …

2     2015-01-25 22:40:24.395 2627   14       insert_data 6       Violation of …

Si quieres jugar con SqlEventLog da click derecho en el punto marcado, podrás descargar el archivo sqleventlog.zip. Para instrucciones sobre su instalación, ver la seccion Installing SqlEventLog en la parte tres.

Observaciones Finales

Has aprendido hasta ahora un patrón general para la gestión de errores y transacciones en procedimientos almacenados.  No es perfecto, pero debería trabajar bien para un 90-95 de tu código.  Hay un par de limitaciones de las que deberás ser consciente:

  1. Como hemos visto, los errores de compilación tales como tablas no encontradas o columnas no encontradas no pueden ser atrapadas en el procedimiento donde ocurren, solamente en los procedimientos externos.
  2. El patrón no trabaja para funciones definidas por el usuario, debido a que TRY-CATCH y RAISERROR no son permitidas ahí.
  3. Cuando llamas a un procedimiento almacenado sobre un servidor enlazado (linked server) que lanza un error, este error podría ser pasado por alto por el manejador de errores en el procedimiento del servidor local e ir directamente al cliente.
  4. Cuando un procedimiento es llamado por INSERT-EXEC, obtendrás un feo error, porque ROLLBACK TRANSACTION no es permitido en este caso.
  5. Como has notado anteriormente, si usaserror_handler_sp o SqlEventLog, perderás un mensaje de error cuando SQL Server lance dos mensajes de error para el mismo error.  Esto no es un problema con ; THROW.

Cubro estas situaciones en mayor detalle en los otros artículos en la serie.

Antes de terminas, me gustaría cubrir brevemente triggers y código de lado del cliente

Triggers

El patrón para la gestión de errores en triggers no es en nada diferente de la gestión de errores en procedimientos almacenados, excepto en un pequeño detalle: no deberás incluir la sentencia RETURN. (Porque RETURN con un valor no es permitido en los triggers).

Lo que es importante entender acerca de los triggers es que ellos son parte de los comandos que encienden el disparador (trigger), y en un trigger tu estas siempre en una transacción, incluso si no usastes BEGIN TRANSACTION.  Algunas veces he visto a personas en los foros de SQL Server preguntar si ellos podían escribir un trigger que no deshace (roll back) el comando que encendió el trigger si el trigger falla.  La respuesta es que no hay manera en la que puedas hacer esto confiable, por lo que lo mejor incluso seria no intentarlo.  Si tienes este tipo de requerimiento, deberías probablemente no usar un trigger en absoluto, pero usar algún otro tipo de solución.  En las partes dos y tres, discuto sobre la gestión de errores en los triggers con más detalle.

Client Code

Si, deberías tener un manejador de errores en el código del lado del cliente que accede a la base de datos. Esto es, deberías siempre asumir que cualquier llamada que haces a la base de datos podría ir mal.  Exactamente como la implementación de un manejador de errores depende de tu entorno, y para cubrir todos los posibles entornos fuera de ahí.  Me gustaría tener que escribir un par de artículos mas, y aprender de todos esos entornos.

Aquí, solo voy a señalar una cosa importante: tu reacción para un error lanzado desde SQL Server debería ser siempre presentar este lote para prevenir transacciones huérfanas:

IF @@trancount > 0 ROLLBACK TRANSACTION

Esto además aplica al famoso mensaje Timeout expired (el cual no es un mensaje de SQL Server, sino del cliente API).

Cubro la gestión de errores en ADO .NET en el último capítulo de la parte (last chapter of Part 3). Si usas la antigua versión ADO, cubro este en mi antiguo artículo sobre gestión de errores en SQL 2000(error handling in SQL 2000).

Fin de la Primera Parte

Este es el fin de la Primera parte de esta serie de artículos.  Si solo quieres aprender rápidamente el patrón, lo haz completado con tu lectura hasta este punto. Si tu intención es leerlo todo, deberías continuar con la Segunda Parte (Part Two) la cual es donde tu viaje a la jungla de la gestión de errores y transacciones en SQL Server iniciara de verdad.

Si tienes cualquier pregunta, comentario o sugerencia específica sobre este artículo, siéntete libre de contactarme a  esquel@sommarskog.se. Esto incluye pequeñas cosas como errores ortográficos, errores gramaticales, errores en mis códigos de ejemplo, etc.  Ya que no tengo un publicista, necesito confiar en mis lectores quienes son mis editores tecnológicos y correctores J .  Si tienes preguntas relacionadas con un problema con el que estés trabajando, te recomiendo realizarla en un foro publico, ya que es más probable que obtengas una respuesta más rápida.

Para una lista de agradecimientos, por favor ver el final de la Tercera Parte (the end of Part Three) .  A continuación una revisión del historial para la Primera Parte.

… y no olvides agregar esta línea como la primera en tus procedimientos almacenados:

SET XACT_ABORT, NOCOUNT ON

Historial de Revisiones

2015-05-03

Primera version (http://www.sommarskog.se/error_handling/Part1.html)

Leave a Reply

Your email address will not be published. Required fields are marked *