Detalle de Objetos Comunes

InforCustomCryptCert

En la base de datos "master" se crea el certificado "InforCustomCryptCert", necesario para la ejecución de las librerías que instala Insert Bulk Bestia, ya que están firmadas con dicho certificado. Se instala en "master" para que cualquier Base de Datos de la instancia pueda utilizar dichas librerías.

Login_InforCustomCryptCert_For_Assemblies

Se crea el inicio de sesión "Login_InforCustomCryptCert_For_Assemblies" basado en el certificado "InforCustomCryptCert". Este inicio de sesión será el habilitado para la ejecución de las librerías instaladas por Insert Bulk Bestia. Mediante este artificio se evita poner la Base de Datos en modo inseguro (TRUSTWORTHY ON).

ASSEMBLY SQLServerSafeFunctions

En las bases de datos origen y destino se crea el "ASSEMBLY SQLServerSafeFunctions". Esta librería contiene funciones para el manejo de cadenas de caracteres con las mejores prestaciones posibles: Split strings the right way – or the next best way

Dichas librerías están basadas en el código: SQLCLR String Splitting Part 2: Even Faster, Even More Scalable

ibbclr.StringToTableStrings

Convierte una cadena de caracteres de valores separados por un separador en una tabla con un registro por cada valor. Los valores vacíos los devuelve como NULL.

Parámetros:

  • @cadena nvarchar(max): NOT NULL. Cadena de caracteres que contiene los valores separados por un separador.
  • @separador nvarchar(255) = N',': NOT NULL. Cadena de caracteres que define el separador.
  • @eliminarSeparadoresConsecutivos INT = 1: NOT NULL. !=0 indica se deben eliminar de la cadena de caracteres los separadores consecutivos.
  • @trim INT = 1: NOT NULL. !=0 indica que se deben eliminar los espacios iniciales y finales de cada valor.

Ejemplo de uso de la función:

SELECT * FROM ibbclr.StringToTableStrings(N',a,     a , ,,,,', DEFAULT, 1, 1)
SELECT * FROM ibbclr.StringToTableStrings(N',a,     a , ,,,,', DEFAULT, 0, 0)

ibbclr.StringToTableStrings - 1

ibbclr.StringToTableStrings - 1

ibbclr.StringToTableStringsPairs

Convierte una cadena de caracteres de parejas de (clave, valor) separadas por un separador de campo y un separador de registros en una tabla con dos campos donde cada registro contiene una pareja (clave, valor).

Parámetros:

  • @cadena nvarchar(max): NOT NULL. Cadena de caracteres que contiene los valores separados por un separador.
  • @separadorCampos nvarchar(255) = N',': NOT NULL. Cadena de caracteres que define el separador entre la clave y el valor.
  • @separadorRegistros nvarchar(255) = N';': NOT NULL. Cadena de caracteres que define el separador entre parejas (clave, valor), es decir, el separador de registros.
  • @eliminarSeparadoresRegistroConsecutivos INT = 1: NOT NULL. !=0 indica que se deben eliminar de la cadena de caracteres los separadores de registros consecutivos.
  • @trim INT = 1: NOT NULL. !=0 indica que se deben eliminar los espacios iniciales y finales de cada valor.

Ejemplo de uso de la función:

DECLARE @separadorRegistros nvarchar(255) = CHAR(13) + CHAR(10), @cadena nvarchar(max) = N'1,2,3,4

1,2,3,
1,2,,4
1,,,4
1'

SELECT * FROM ibbclr.StringToTableStringsPairs(@cadena, DEFAULT, @separadorRegistros, 1, 1)
SELECT * FROM ibbclr.StringToTableStringsPairs(@cadena, DEFAULT, @separadorRegistros, 0, 0)

SELECT Primero.cadena1 AS Clase, Segundo.cadena1 AS Valor_Clase, Segundo.cadena2
  FROM ibbclr.StringToTableStringsPairs(@cadena, DEFAULT, @separadorRegistros, 1, 1) Primero
    CROSS APPLY ibbclr.StringToTableStringsPairs(ISNULL(Primero.cadena2, N','), DEFAULT, @separadorRegistros, 1, 1) Segundo

SELECT Primero.cadena1 AS Clase, Segundo.cadena1 AS Valor_Clase, Tercero.cadena1 AS Campo, Tercero.cadena2 AS Valor_Campo
  FROM ibbclr.StringToTableStringsPairs(@cadena, DEFAULT, @separadorRegistros, 1, 1) Primero
    CROSS APPLY ibbclr.StringToTableStringsPairs(ISNULL(Primero.cadena2, N','), DEFAULT, DEFAULT, 1, 1) Segundo
    CROSS APPLY ibbclr.StringToTableStringsPairs(ISNULL(Segundo.cadena2, N','), DEFAULT, DEFAULT, 1, 1) Tercero

ibbclr.StringToTableStringsPairs - 1

ibbclr.StringToTableStringsPairs - 1

ibb.Log_Errores

En las bases de datos de origen y destino se crea la tabla 'ibb.Log_Errores'. Esta tabla es utilizada por Insert Bulk Bestia para almacenar las acciones que realiza en cada momento y los posibles errores que se puedan ejecutar en los procedimientos almacenados. La inserción en esta tabla se realiza mediante el procedimiento almacenado "ibb.Log_Errores_Inserta".

Campo Tipo NULL Restricción Descripción
RunID bigint identity(1, 1) NOT NULL Campo identidad
RunDate datetime2(7) NOT NULL Log_Errores_FechaLog_DEF DEFAULT (SYSDATETIME()) Fecha de creación del registro
MessageText nvarchar(max) NOT NULL Mensaje de Log o Error
LevelLog tinyint NOT NULL Log_Errores_LevelLog_DEF DEFAULT (0) Nivel del error. IBB: 0 información, 10 error
Clasificacion_Log_Error nvarchar(256) NULL Campo descriptivo para indicar el proceso en el que se produjo el error. IBB anotará en este campo 'InsertBulkBestia', indicando así, que el mensaje se insertó durante la ejecución de dicho proceso.
Duration nvarchar(50) NULL Duración de un determinado proceso. IBB lo utiliza para indicar la duración de cada procedimiento almacenado que ejecuta.
DBName sysname NOT NULL Nombre de la Base de Datos desde dónde se insertó el registro.
SchemaName sysname NULL Nombre del esquema del objeto desde dónde se insertó el registro.
ObjectName sysname NULL Nombre del objeto desde dónde se insertó el registro.
ERR_SQL_NUM_ERROR int NULL Número del error.
ERR_SQL_NUM_LINEA int NULL Línea donde se ha producido el error.
ERR_SQL_MENSAJE nvarchar(max) NULL Mensaje del error.
ERR_SQL_PROCEDIMIENTO sysname NULL Procedimiento dónde se ha producido el error.
ERR_SQL_SEVERIDAD int NULL Severidad del error.
ERR_SQL_ESTADO int NULL Estado del error.
IDSession smallint NOT NULL Log_Errores_IdSesion_DEF DEFAULT (@spid) Identificador de la sesión.
LoginTimeSesion datetime2(3) NOT NULL Log_Errores_FechaSesion_DEF DEFAULT (ibb.Log_Errores_Time_Login_Session(@spid)) Fecha y hora de inicio de la sesión.
LoginNameSesion sysname NOT NULL Log_Errores_LoginNameSesion_DEF DEFAULT (ibb.Log_Errores_Login_Name_Session(@spid)) Login de la sesión.
OriginalLoginNameSesion sysname NOT NULL Log_Errores_OriginalLoginNameSesion_DEF DEFAULT (ibb.Log_Errores_Original_Login_Name_Session(@spid)) Original Login de la sesión.
ValorDeSesion nvarchar(4000) NULL Log_Errores_ValorSesion_DEF DEFAULT (ibb.Log_Errores_Valor_Session()) Valor de contexto de la sesión.

Asociadas a esta tabla, existen una serie de funciones utilizadas en las restricciones o bien en la inserción de registros:

ibb.Log_Errores_Time_Login_Session

Función que devuelve la fecha y hora del inicio de sesión.

ibb.Log_Errores_Original_Login_Name_Session

Función que devuelve el original_login_name del inicio de sesión.

ibb.Log_Errores_Login_Name_Session

Función que devuelve el login_name de la sesión.

ibb.Constantes

Vista utilizada para definir un nombre de clave para poderla usar en el contexto de sesión. Insert Bulk Bestia define ese nombre como 'NombreUsuario' para el campo 'ClaveSessionContext'.

ibb.Log_Errores_Valor_Session

Extrae del contexto de sesión el valor asociado a la clave definida en la vista anterior: 'NombreUsuario'.

En muchos sistemas, el control de usuarios finales se desarrolla a medida, y las conexiones a la base de datos se realiza utilizando un sólo usuario de base de datos. Mediante el contexto de sesión, entre otras cosas, se va a poder indicar el usuario final que se está conectando. Así, por ejemplo, si tras abrir una conexión a SQL Server se ejecutan las siguientes instrucciones:

DECLARE @Valor AS sysname = (SELECT ClaveSessionContext FROM ibb.Constantes)
EXEC sp_set_session_context @Valor, @ParametroConNombreDeUsuario, 1

Se tendrá, durante todo el tiempo que dure la sesión, almacenado el nombre del usuario que la ha abierto. Este nombre será almacenado en el campo 'ValorDeSesion' cuando se inserte un registro en la tabla 'ibb.Log_Errores'.

Para más información vea: SESSION_CONTEXT y sp_set_session_context

ibb.TimeSpanToNow

Función que calcula la diferencia de tiempo entre el valor pasado como parámetro y el valor actual. El valor devuelto es una cadena de caracteres con el formato D:HH:MM:SS.MS (días-horas-minutos-segundos-milisegundos).

Es la función utilizada para anotar la duración de la ejecución de los procedimientos almacenados. En la declaración de las variables de los mismos se crea una variable que almacene el inicio de la ejecución (DECLARE @inicio DATETIME2(7) = SYSDATETIME()). Al final del procedimiento almacenado, al insertar en 'ibb.Log_Errores' el registro de LOG correspondiente, se utilizará esta función y esta variable para calcular el tiempo de ejecución del procedimiento almacenado.

ibb.Log_Errores_Inserta

Procedimiento almacenado utilizado para la inserción de registros en la tabla 'ibb.Log_Errores'.

Parámetros:

  • @MessageText nvarchar(max): Mensaje que se desea incluir en el campo 'MessageText'
  • @LevelLog tinyint = 0: Nivel del error. Se incluye en el campo 'LevelLog'. Insert Bulk Bestia inserta un 0 para los mensajes informativos y un 10 en los mensajes de error.
  • @Clasificacion_Log_Error nvarchar(256) = NULL: Campo descriptivo para indicar el proceso en el que se produjo el error o se anotó el Log. IBB anotará en este campo el valor 'InsertBulkBestia', indicando así, que el mensaje se insertó durante la ejecución de dicho proceso.
  • @HoraInicio datetime2(7) = NULL: Fecha y Hora en la que se inició un determinado proceso. Si este parámetro no es nulo, la función calculará la diferencia de tiempo entre el parámetro y el momento de insertar el registro en 'ibb.Log_Errores' y lo anotará en el campo 'Duration'.
  • @DBName sysname = NULL: Nombre de la base de datos.
  • @SchemaName sysname = NULL: Nombre del esquema del procedimiento almacenado desde donde es llamado este procedimiento.
  • @ObjectName sysname = NULL: Nombre del procedimiento almacenado desde donde es llamado este procedimiento.
  • @ERR_SQL_NUM_ERROR int = NULL:Si se desea insertar el número de error, debería incluir el valor de la función ERROR_NUMBER().
  • @ERR_SQL_NUM_LINEA int = NULL: Si se desea insertar el número de linea donde se ha producido el error, debería incluir el valor de la función ERROR_LINE().
  • @ERR_SQL_MENSAJE nvarchar(max) = NULL: Si se desea insertar un mensaje de error, debería incluir el valor de la función ERROR_MESSAGE().
  • @ERR_SQL_PROCEDIMIENTO sysname = NULL: Si se desea insertar el nombre del procedimiento almacenado donde se ha producido el errror, debería incluir el valor de la función ERROR_PROCEDURE().
  • @ERR_SQL_SEVERIDAD int = NULL: Si se desea insertar la severidad del error, debería incluir el valor de la función ERROR_SEVERITY().
  • @ERR_SQL_ESTADO int = NULL: Si se desea insertar el estado del error, debería incluir el valor de la función ERROR_STATE().
  • @ValorSesion nvarchar(4000) = NULL: Valor a incluir en el campo 'ValorDeSesion'. Si es NULL, se incluirá el valor devuelto por la función 'ibb.Log_Errores_Valor_Session()'

Ejemplo de uso:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.Ejemplo
  @error INT OUTPUT
AS BEGIN SET NOCOUNT ON

  --Declaración de variables con información a insertar en 'ibb.Log_Errores'
  DECLARE @inicio DATETIME2(7) = SYSDATETIME(),
    @nameDB SYSNAME = DB_NAME(),
    @esquema SYSNAME = OBJECT_SCHEMA_NAME(@PROCID),
    @procedimiento SYSNAME = OBJECT_NAME(@PROCID)

  SELECT @error = 0
  BEGIN TRY
    --....
    --....
    --....

    --El Procedimiento almacenado finaliza correctamente:
    --  Anotar un registro de LOG:
    --    Mensaje, LevelLog 0, Proceso al que pertenece, Inicio de la ejecución para calcular la duración,
    --    Nombre de la Base de Datos, Esquema y Nombre del Procedimiento Almacenado
    EXECUTE ibb.Log_Errores_Inserta N'Se ha ejecutado el Ejemplo.', 0, N'ProcesoEjemplo', @inicio, @nameDB, @esquema, @procedimiento

  END TRY
  BEGIN CATCH
    DECLARE @ERR_SQL_NUM_ERROR INT = ERROR_NUMBER(),
      @ERR_SQL_NUM_LINEA INT = ERROR_LINE(),
      @ERR_SQL_MENSAJE NVARCHAR(MAX) = ERROR_MESSAGE(),
      @ERR_SQL_PROCEDIMIENTO SYSNAME = ERROR_PROCEDURE(),
      @ERR_SQL_SEVERIDAD INT = ERROR_SEVERITY(),
      @ERR_SQL_ESTADO INT = ERROR_STATE()

    --Procedimiento almacenado finaliza con errores:
    --  Anotar un registro de ERROR:
    --    Además de los datos anteriores, se anota el Nº del Error, la Línea dónde se ha producido,
    --    el Mensaje del Error, el Procedimiento donde se ha producido, la Severidad y el Estado.
    EXECUTE ibb.Log_Errores_Inserta N'Se ha producido una excepción en Ejemplo.',
      10, N'ProcesoEjemplo', NULL, @nameDB, @esquema, @procedimiento,
      @ERR_SQL_NUM_ERROR, @ERR_SQL_NUM_LINEA, @ERR_SQL_MENSAJE,
      @ERR_SQL_PROCEDIMIENTO, @ERR_SQL_SEVERIDAD, @ERR_SQL_ESTADO

    --En el parámetro de salida se anota el error.
    SELECT @error = @ERR_SQL_NUM_ERROR
  END CATCH
END
GO

ASSEMBLY SQLServerAutonomousCalls

Si el tipo de licencia lo permite, en las bases de datos origen y destino se crea este ensamblado. Contiene una función que va a permitir almacenar en 'ibb.Log_Errores' un registro simulando las transacciones autónomas de Oracle. En otras palabras, hará que dicho registro no se elimine, aunque se ejecute un ROLLBACK de la transacción en la que la inserción de dicho registro se encuentre inmersa.

La utilidad de esta librería es la de permitir un traceado de lo que ha sucedido en un determinado proceso, aunque dicho proceso haya finalizado con un ROLLBACK.

ibbclr.Log_Errores_Inserta_TrnAut_CLR

Procedimiento almacenado que permite la simulación de las transacciones autónomas de Oracle para la inserción en 'ibb.Log_Errores'.

ibb.Log_Errores_Inserta

Nueva función que sustituye a la anterior. Los parámetros y su funcionalidad es la misma.

ibb.Log_Errores_Inserta - 1

ibb.Log_Errores_Inserta - 1

Estrategia de Control de Transacciones en Insert Bulk Bestia

Se basa en cuatro principios:

  • En un procedimiento almacenado, el número de transacciones al inicio debe ser el mismo que al final. Impuesto por SQL Server.
  • Un procedimiento almacenado no abre ni cierra ninguna transacción si ya existe una transacción abierta.
  • Un procedimiento almacenado que abre una transacción será el responsable de cerrarla, ya sea con un commit o un rollback.
  • Todos los procedimientos almacenados poseen un parámetro de salida indicando si dicho procedimiento almacenado se ha ejecutado correctamente o no.

De acuerdo con estos principios, y con la librería 'SQLServerAutonomousCalls', la estructura básica de cualquier procedimiento almacenado usado por Insert Bulk Bestia es similar a la mostrada a continuación:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.Ejemplo
  @error INT OUTPUT
AS BEGIN SET NOCOUNT ON

  --Declaración de variables con información a insertar en 'ibb.Log_Errores'
  DECLARE @inicio DATETIME2(7) = SYSDATETIME(),
    @nameDB SYSNAME = DB_NAME(),
    @esquema SYSNAME = OBJECT_SCHEMA_NAME(@PROCID),
    @procedimiento SYSNAME = OBJECT_NAME(@PROCID)

  --Obtención del número de transacciones abiertas al inicio
  DECLARE @NumTranAntes INT = @TRANCOUNT


  SELECT @error = 0
  BEGIN TRY
    --Si no hay transacciones abiertas, el Procedimiento Almacenado abre su transacción, de la cual será responsable.
    IF @NumTranAntes = 0 BEGIN TRAN

    --....
    --....
    --....

    --Si todo ha funcionado como se espera, y no había transacciones abiertas al inicio y hay alguna transacción abierta en este momento,
    --  realiza un COMMIT, ya que este procedimiento almacenado es el responsable de su apertura.
    IF @NumTranAntes = 0 AND @TRANCOUNT > 0 COMMIT TRAN


      --Si no se siguen los principios de Insert Bulk Bestia se pueden aplicar las siguientes instrucciones
      --WHILE @TRANCOUNT > 0 COMMIT TRAN
      --WHILE @TRANCOUNT < @NumTranAntes BEGIN TRAN


        --Si no se siguen los principios de Insert Bulk Bestia se pueden aplicar
        --las siguientes instrucciones divididas en dos partes si no se dispone
        --de SQLServerAutonomousCalls:
        --  PARTE 1
        --WHILE @TRANCOUNT > 0 COMMIT TRAN


    --El Procedimiento almacenado finaliza correctamente:
    --  Anotar un registro de LOG:
    --    Mensaje, LevelLog 0, Proceso al que pertenece, Inicio de la
    --    ejecución para calcular la duración, Nombre de la Base de Datos,
    --    Esquema y Nombre del Procedimiento Almacenado
    EXECUTE ibb.Log_Errores_Inserta N'Se ha ejecutado el Ejemplo.',
      0, N'ProcesoEjemplo', @inicio, @nameDB, @esquema, @procedimiento

         --Si no se siguen los principios de Insert Bulk Bestia se pueden aplicar
        --las siguientes instrucciones divididas en dos partes si no se dispone
        --de SQLServerAutonomousCalls:
        --  PARTE 1
        --WHILE @TRANCOUNT < @NumTranAntes BEGIN TRAN


  END TRY
  BEGIN CATCH
    DECLARE @ERR_SQL_NUM_ERROR INT = ERROR_NUMBER(),
      @ERR_SQL_NUM_LINEA INT = ERROR_LINE(),
      @ERR_SQL_MENSAJE NVARCHAR(MAX) = ERROR_MESSAGE(),
      @ERR_SQL_PROCEDIMIENTO SYSNAME = ERROR_PROCEDURE(),
      @ERR_SQL_SEVERIDAD INT = ERROR_SEVERITY(),
      @ERR_SQL_ESTADO INT = ERROR_STATE()

    --Si NO ha funcionado como se espera, y no había transacciones
    --  abiertas al inicio y hay alguna transacción abierta en este momento,
    --  realiza un ROLLBACK, ya que este procedimiento almacenado es el responsable de su apertura.
    --NOTA: ROLLBACK deshace TODAS las transacciones abiertas
    --NOTA: Si había transacciones abiertas al inicio, el ROLLBACK no se producirá.
    IF @NumTranAntes = 0 AND @TRANCOUNT > 0 ROLLBACK TRAN


         --Si no se dispone de SQLServerAutonomousCalls y se desea dejar constancia del error en ibb.Log_Errores:
        --  PARTE 1
        --IF @@TRANCOUNT > 0 ROLLBACK TRAN


    --Procedimiento almacenado finaliza con errores:
    --  Anotar un registro de ERROR:
    --    Además de los datos anteriores, se anota el Nº del Error, la
    --    Línea dónde se ha producido, el Mensaje del Error, el Procedimiento
    --    donde se ha producido, la Severidad y el Estado.
    EXECUTE ibb.Log_Errores_Inserta N'Se ha producido una excepción en Ejemplo.',
      10, N'ProcesoEjemplo', NULL, @nameDB, @esquema, @procedimiento,
      @ERR_SQL_NUM_ERROR, @ERR_SQL_NUM_LINEA, @ERR_SQL_MENSAJE,
      @ERR_SQL_PROCEDIMIENTO, @ERR_SQL_SEVERIDAD, @ERR_SQL_ESTADO

    --Dado que el Nº de transacciones abiertas debe ser igual al inicio y final
    --del procedimiento almacenado, tras el ROLLBACK se ha de igualar dicho número.
    --NOTA: Si había transacciones abiertas al inicio, éstas permanecerán invariantes
    WHILE @TRANCOUNT < @NumTranAntes BEGIN TRAN


        --Si no se dispone de SQLServerAutonomousCalls y se desea dejar constancia del error en ibb.Log_Errores:
        --  PARTE 2
        --WHILE @TRANCOUNT < @NumTranAntes BEGIN TRAN


    --En el parámetro de salida se anota el error.
    SELECT @error = @ERR_SQL_NUM_ERROR

  END CATCH
END
GO

Evidentemente, existen otras estrategias posibles, como la utilización de la instrucción THROW en la captura de errores, contar con los dedos el número de transacciones abiertas en el momento de codificar los procedimientos almacenados, anotar en una hoja de papel el flujo seguido por el sistema, etc.

Sin embargo, en Insert Bulk Bestia se opta por simplificar al máximo la complejidad del flujo del sistema final. Así, con estos sencillos principios y la librería 'SQLServerAutonomousCalls', en sistemas altamente complejos, se tiene un control perfecto y sencillo, al mismo tiempo que se obtiene un traceado completo de lo que sucede en cada momento en el sistema.

Además, una de las ventajas de la tabla 'ibb.Log_Errores' y esta estrategia, es que permite encontrar fácilmente la degradación de las prestaciones de un sistema o proceso, ya que contendrá el histórico de ejecuciones de cada procedimiento almacenado y por tanto el histórico de los tiempos de procesado del mismo. Ello permite ir directamente a optimizar aquella parte del sistema o proceso que se ha degradado.

Por supuesto, el exhaustivo control de errores que esta estrategia permite, ahorra muchísimo tiempo durante el proceso de depuración de un desarrollo. Y por el contrario, no supone ningún esfuerzo adicional en los desarrollos, ya que se puede tener una "plantilla" base para los procedimientos almacenados a partir de la cual comenzar el desarrollo. Sin contar que, dicha plantilla base, permitirá homogeneizar la estructura del código en el sistema.