Detail of Common Objects

Name: Insert Bulk Bestia
Author: InforCustom

InforCustomCryptCert

In the "master" database, the "InforCustomCryptCert" certificate is created, which is necessary to run the libraries installed by Insert Bulk Bestia, since they are signed with said certificate. It is installed in "master" so that any Database of the instance can use these libraries.

Login_InforCustomCryptCert_For_Assemblies

The "Login_InforCustomCryptCert_For_Assemblies" login is created based on the "InforCustomCryptCert" certificate. This login will be the one enabled for the execution of the libraries installed by Insert Bulk Bestia. By means of this artifice it is avoided to put the Database in insecure mode (TRUSTWORTHY ON).

ASSEMBLY SQLServerSafeFunctions

In the source and target databases the "ASSEMBLY SQLServerSafeFunctions" is created. This library contains functions for handling character strings with the best possible features: Split strings the right way – or the next best way

These libraries are based on the code: SQLCLR String Splitting Part 2: Even Faster, Even More Scalable

ibbclr.StringToTableStrings

Converts a string of values separated by a separator into a table with one record for each value. Empty values are returned as NULL.

Parameters:

@cadena nvarchar(max): NOT NULL. Character string containing the values separated by a separator.
@separador nvarchar(255) = N',': NOT NULL. String of characters that defines the separator.
@eliminarSeparadoresConsecutivos INT = 1: NOT NULL. !=0 indicates consecutive separators must be removed from the character string.
@trim INT = 1: NOT NULL. !=0 indicates that leading and trailing spaces should be removed from each value.

Function use example:


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

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

ibbclr.StringToTableStrings - 1

ibbclr.StringToTableStringsPairs

Converts a string of (key, value) pairs separated by a field separator and a record separator into a table with two fields where each record contains a pair (key, value).

Parameters:

@cadena nvarchar(max): NOT NULL. Character string containing the values separated by a separator.
@separadorCampos nvarchar(255) = N',': NOT NULL. Character string that defines the separator between the key and the value.
@separadorRegistros nvarchar(255) = N';': NOT NULL. String of characters that defines the separator between pairs (key, value), that is, the record separator.
@eliminarSeparadoresRegistroConsecutivos INT = 1: NOT NULL. !=0 indicates that consecutive record separators should be removed from the character string.
@trim INT = 1: NOT NULL. !=0 indicates that leading and trailing spaces should be removed from each value.

Function use example:


  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

ibb.Log_Errores

In the source and destination databases the table 'ibb.Log_Errores' is created. This table is used by Insert Bulk Bestia to store the actions it performs at any given time and the possible errors that may be executed in the stored procedures. Insertion into this table is done using the stored procedure "ibb.Log_Errores_Inserta".

Field	Type	NULL	Check	Description
RunID	bigint identity(1, 1)	NOT NULL		Identity field
RunDate	datetime2(7)	NOT NULL	Log_Errores_FechaLog_DEF DEFAULT (SYSDATETIME())	Record creation date
MessageText	nvarchar(max)	NOT NULL		Log or Error Message
LevelLog	tinyint	NOT NULL	Log_Errores_LevelLog_DEF DEFAULT (0)	Error level. IBB: 0 information, 10 error
Clasificacion_Log_Error	nvarchar(256)	NULL		Descriptive field to indicate the process in which the error occurred. IBB will note in this field 'InsertBulkBestia', thus indicating that the message was inserted during the execution of said process.
Duration	nvarchar(50)	NULL		Duration of a certain process. IBB uses it to indicate the duration of each stored procedure that it executes.
DBName	sysname	NOT NULL		Name of the Database from where the record was inserted.
SchemaName	sysname	NULL		Schema name of the object from where the record was inserted.
ObjectName	sysname	NULL		Name of the object from where the record was inserted.
ERR_SQL_NUM_ERROR	int	NULL		Error number.
ERR_SQL_NUM_LINEA	int	NULL		Line where the error occurred.
ERR_SQL_MENSAJE	nvarchar(max)	NULL		Error message.
ERR_SQL_PROCEDIMIENTO	sysname	NULL		Procedure where the error occurred.
ERR_SQL_SEVERIDAD	int	NULL		Severity of the error.
ERR_SQL_ESTADO	int	NULL		Error status.
IDSession	smallint	NOT NULL	Log_Errores_IdSesion_DEF DEFAULT (@spid)	Session identifier.
LoginTimeSesion	datetime2(3)	NOT NULL	Log_Errores_FechaSesion_DEF DEFAULT (ibb.Log_Errores_Time_Login_Session(@spid))	Session start date and time.
LoginNameSesion	sysname	NOT NULL	Log_Errores_LoginNameSesion_DEF DEFAULT (ibb.Log_Errores_Login_Name_Session(@spid))	Session login.
OriginalLoginNameSesion	sysname	NOT NULL	Log_Errores_OriginalLoginNameSesion_DEF DEFAULT (ibb.Log_Errores_Original_Login_Name_Session(@spid))	Original Login of the session.
ValorDeSesion	nvarchar(4000)	NULL	Log_Errores_ValorSesion_DEF DEFAULT (ibb.Log_Errores_Valor_Session())	Session context value.

Associated with this table, there are a series of functions used in the restrictions or in the insertion of records:

ibb.Log_Errores_Time_Login_Session

Function that returns the date and time of the login.

ibb.Log_Errores_Original_Login_Name_Session

Function that returns the original_login_name of the login.

ibb.Log_Errores_Login_Name_Session

Function that returns the login_name of the session.

ibb.Constantes

View used to define a key name so that it can be used in the session context. Insert Bulk Bestia define that name as 'NombreUsuario' for field 'ClaveSessionContext'.

ibb.Log_Errores_Valor_Session

Extracts from the session context the value associated with the key defined in the previous view: 'NombreUsuario'.

In many systems, end-user control is custom developed, and connections to the database are made using a single database user. Through the session context, among other things, it will be possible to indicate the end user who is connecting. Thus, for example, if after opening a connection to SQL Server the following instructions are executed:


  DECLARE @Valor AS sysname = (SELECT ClaveSessionContext FROM ibb.Constantes)

  EXEC sp_set_session_context @Valor, @ParametroConNombreDeUsuario, 1

The name of the user who opened it will be stored for the entire duration of the session. This name will be stored in the 'ValordeSesion' field when a record is inserted into the table 'ibb.Log_Errores'.

For more information see: SESSION_CONTEXT and sp_set_session_context

ibb.TimeSpanToNow

Function that calculates the time difference between the value passed as a parameter and the current value. The return value is a character string in the format D:HH:MM:SS.MS (days-hours-minutes-seconds-milliseconds).

It is the function used to record the duration of the execution of stored procedures. In the declaration of their variables, a variable is created that stores the start of execution (DECLARE @inicio DATETIME2(7) = SYSDATETIME()). At the end of the stored procedure, when inserting the corresponding LOG record in 'ibb.Log_Errores', this function and this variable will be used to calculate the execution time of the stored procedure.

ibb.Log_Errores_Inserta

Stored procedure used for inserting records into table 'ibb.Log_Errores'.

Parámetros:

@MessageText nvarchar(max): Message to include in the field 'MessageText'
@LevelLog tinyint = 0: Error level. Included in the field 'LevelLog'. Insert Bulk Bestia inserts a 0 for informational messages and a 10 for error messages.
@Clasificacion_Log_Error nvarchar(256) = NULL: Descriptive field to indicate the process in which the error occurred or the Log was recorded. IBB will write down the value 'InsertBulkBestia' in this field, thus indicating that the message was inserted during the execution of said process.
@HoraInicio datetime2(7) = NULL: Date and Time when a certain process started. If this parameter is not null, the function will calculate the time difference between the parameter and the time of inserting the record in 'ibb.Log_Errores' and will note it in the 'Duration' field.
@DBName sysname = NULL: Database name.
@SchemaName sysname = NULL: Name of the stored procedure schema from where this procedure is called.
@ObjectName sysname = NULL: Name of the stored procedure from where this procedure is called.
@ERR_SQL_NUM_ERROR int = NULL: If you want to insert the error number, you should include the value of the function ERROR_NUMBER().
@ERR_SQL_NUM_LINEA int = NULL: If you want to insert the line number where the error occurred, you should include the value of the function ERROR_LINE().
@ERR_SQL_MENSAJE nvarchar(max) = NULL: If you want to insert an error message, you should include the value of the function ERROR_MESSAGE().
@ERR_SQL_PROCEDIMIENTO sysname = NULL: If you want to insert the name of the stored procedure where the error occurred, you should include the value of the function ERROR_PROCEDURE().
@ERR_SQL_SEVERIDAD int = NULL: If you want to insert the severity of the error, you should include the value of the function ERROR_SEVERITY().
@ERR_SQL_ESTADO int = NULL: If you want to insert the error status, you should include the value of the function ERROR_STATE().
@ValorSesion nvarchar(4000) = NULL: Value to include in the 'ValorDeSesion' field. If it is NULL, the value returned by the function will be included 'ibb.Log_Errores_Valor_Session()'

Example of use:


  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

If the license type allows it, this assembly is created in the source and target databases. It contains a function that will allow a record to be stored in 'ibb.Log_Errores' simulating Oracle's autonomous transactions. In other words, it will ensure that said record is not deleted, even if a ROLLBACK of the transaction in which the insertion of said record is immersed is executed.

The usefulness of this library is to allow a trace of what has happened in a certain process, even if said process has ended with a ROLLBACK.

ibbclr.Log_Errores_Inserta_TrnAut_CLR

Stored procedure that enables the simulation of autonomous Oracle transactions for insertion into 'ibb.Log_Errores'.

ibb.Log_Errores_Inserta

New function that replaces the previous one. The parameters and their functionality is the same.

ibb.Log_Errores_Inserta - 1

Transaction Control Strategy in Insert Bulk Bestia

It is based on four principles:

In a stored procedure, the number of transactions at the beginning must be the same as at the end. Laid by SQL Server.
A stored procedure does not open or close a transaction if an open transaction already exists.
A stored procedure that opens a transaction will be responsible for closing it, either with a commit or a rollback.
All stored procedures have an output parameter indicating whether the stored procedure has been successfully executed or not.

In accordance with these principles, and with the 'SQLServerAutonomousCalls' library, the basic structure of any stored procedure used by Insert Bulk Beast is similar to that shown below:


  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

Obviously, there are other possible strategies, such as using the THROW instruction to capture errors, counting the number of open transactions at the time of coding stored procedures on the fingers, writing down the flow followed by the system on a sheet of paper, etc.

However, Insert Bulk Bestia opts to simplify as much as possible the complexity of the flow of the final system. Thus, with these simple principles and the 'SQLServerAutonomousCalls' library, in highly complex systems, you have a perfect and simple control, at the same time that you get a complete trace of what happens at all times in the system.

In addition, one of the advantages of the 'ibb.Log_Errores' table and this strategy is that it allows you to easily find the degradation of the performance of a system or process, since it will contain the execution history of each stored procedure and therefore the historical of the processing times of the same. This allows you to go directly to optimize that part of the system or process that has been degraded.

Of course, the exhaustive error control that this strategy allows saves a lot of time during the debugging process of a development. And on the contrary, it does not suppose any additional effort in the developments, since it is possible to have a base "template" for the stored procedures from which to begin the development. Not to mention that, said base template, will allow to homogenize the structure of the code in the system.