[SQL 2008] Manipulación de Errores MS SQL Server 2008.

Saludos amigos, nuevamente les dejo un tema que traduje y aprendí, para el manejo de Errores en SQL Server 2008, considero es muy bueno y que será para algunos la opción perfecta a utilizar en nuestras aplicaciones de bases de datos.

Mensajes de Error definidos por el Sistema y definidos por el Usuario.

Este tema se refiere a la visualización de errores definidos por el sistema y la agregación de mensajes de error definidos por el usuario a la instancia de SQL Server.

La tabla sys.messages contiene una fila por cada mensaje de error definido por el usuario y mensajes integrados en la instancia de SQL Server.

Los mensajes de error integrados son aquellos que se plantearon en respuesta a los errores estándar de SQL Server.

Los mensajes de error definidos por el usuario se utilizan comúnmente en aplicaciones de terceros (third-party applications), que definen un conjunto de mensajes de error para usarse dentro de una aplicación. Los mensajes de error definidos por el usuario permiten la parametrización, lo que significa que puede crear sus propios mensajes, los cuales permitirán una personalización basada en parámetros.

Visualizando la Información de Error del Sistema.

Para esto puede utilizar la vista del catalogo de sistema sys.messages para ver todos los mensajes de error del sistema y los definidos por el usuario en la instancia de SQL Server, como se muestra a continuación.

SELECT m.message_id, m.severity, m.is_event_logged, m.[text]
FROM sys.messages m
INNER JOIN sys.syslanguages l ON m.language_id = l.msglangid 
WHERE l.alias = 'Spanish'
ORDER BY m.severity DESC, m.[text]

En la consulta anterior solo se mostraran los mensajes del lenguaje Español, el resultado filtrado para fines de ejemplo seria el siguiente:

Explicando la tabla:

• message_id: Identificador del mensaje.
• severity: Nivel de gravedad.
• is_event_logged: Se usa si el error se escribe en el registro de eventos de Windows.
• text: Texto del mensaje.

Los rangos de nivel de gravedad “Severity” son de 1 a 25, con las siguientes categorizaciones implícitas:

0 al 10: Solo son mensajes informativos.

11 al 16: Son errores de motor de base de datos que pueden ser corregidos por el usuario, como son los objetos de la base de datos que faltan cuando se ejecuta la consulta, sugerencias de bloqueo no compatibles, permisos denegados, bloqueos de transacción y errores de sintaxis. Por ejemplo, una violación de llave primaria “PRIMARY KEY” devolverá un error de nivel de gravedad 14. Un error de división entre cero devuelve un error de nivel de gravedad 16.

17 al 19: Son errores que necesitan ser atendidos por el sysadmin, por ejemplo, si SQL Server se ha quedado sin recursos de memoria, o si se han alcanzado los límites del motor de base de datos.

20 al 25: Son errores fatales y de problemas del sistema, como los son hardware o software dañado que afecta a la base de datos, problemas de integridad y errores de los medios de comunicación.

Como pudieron  notar la columna de texto en sys.messages contiene el mensaje de error real que se presenta al usuario desde el motor de base de datos. Observe que algunos mensajes tienen signos de porcentaje y otros símbolos combinados dentro de ellos:

El signo % es un parámetro de sustitución que permite al motor de base de datos personalizar la salida del mensaje de error basado en el contexto actual de la base de datos y error de evento. Los valores concatenados para el signo % indican el tipo de dato y la longitud del parámetro de sustitución.

Creación de un mensaje de error definido por el usuario.

Para crear un nuevo mensaje de error definido por el usuario se hace utilizando el procedimiento almacenado de sistema sp_addmessage, tal vez desee crear sus propios mensajes de error personalizados para sus aplicaciones, y a si garantizar la coherencia a través de rutinas para el manejo de errores específicos de aplicación.

Para crear un nuevo mensaje de error y agregarlo a la vista de sistema sys.messages, tendremos que ejecutar el siguiente procedimiento almacenado “sp_addmessage”, para que posteriormente lo invoquemos con el comando RAISERROR().

Sintaxis:

sp_addmessage [ @msgnum = ] msg_id ,

[ @severity = ] severity ,

[ @msgtext = ] ‘msg’

[ , [ @lang = ] ‘language’ ]

[ , [ @with_log = ] ‘with_log’ ]

[ , [ @replace = ] ‘replace’ ]

Descripción de los parámetros:

msg_id: Es el identificador del error proporcionado por el usuario, que puede ser entre 50,001 y 2,147,483,647. El ID del mensaje no es de la clave única o llave primaría de la tabla; en su lugar, la clave única esta compuesta por la combinación del ID del mensaje y el ID de idioma.

• severity: Define el nivel de gravedad del mensaje (1 a 25).
• msg: Representa el mensaje de error real, que utiliza un tipo de datos nvarchar(255).
• language: Especifica el idioma en el que está escrito el mensaje de error.
• with_log: Esto define si el mensaje se escribirá o no en el Registro de errores de aplicación para Windows cuando se invoque el error.
• Replace: Cuando se especifica,  El existente error definido por el usuario (basado en ID del mensaje y el lenguaje) es sobrescrito con los nuevos parámetros pasados al procedimiento almacenado del sistema.

Ahora crearemos nuestro propio mensaje de error definido por el usuario:

Nota importante: En mi caso estoy trabajando sobre un servidor configurado con Español (Spanish), y al intentar crear un mensajes de Error, me sale el siguiente mensaje de error:

Para esto debemos primero crear una versión de nuestro mensaje de error para el lenguaje Ingles (English), esto es por que el lenguaje nativo de SQL Server es el Ingles.

Ahora si creemos nuestro mensaje de error:

• Creando el mensaje en Ingles:

-- Creando un mensaje de error en Ingles.
EXEC sp_addmessage  
70003,
14,
N'You dont have permission in the current table %s contact the database administrator.', 'English'
GO

• Creando el mensaje en Español:

-- Creando un mensaje de error en Español.
EXEC sp_addmessage 
70003,
14,
N'No tiene permisos en la tabla actual %1! contacte al administrador de base de datos.', 'Spanish'
GO

• Invocando los mensajes de error con RAISERROR():

-- Usando el mensaje creado con el comando RAISERROR.
-- Ingles
SET LANGUAGE us_english;
RAISERROR (70003, 14, 1, N'dbo.DEMO_ROLES')

-- Español
SET LANGUAGE Español;
RAISERROR (70003, 14, 1, N'dbo.DEMO_ROLES')

Resultado:

Por último vale la pena mencionar que para crear los parámetros en versiones no inglesas, deben utilizar números seguidos del signo de exclamación “!” que coincidan con los parámetros del mensaje original como lo muestro en el siguiente ejemplo:

Casi lo olvido, falta indicar como eliminar un mensaje de error, esto se realiza con la siguiente sintaxis:

sp_dropmessage [ @msgnum = ] message_number

[ , [ @lang = ] ‘language’ ]

Ejemplo:

EXEC sp_dropmessage 70003

Espero se de ayuda para ustedes, por favor dejen comentarios y apóyenme votando por mi blog.

 

Apóyame votando aquí: http://blogit.ms/TopBloggers.aspx en la categoría SQL Server. Los pasos para votar están aquí te llevara 3 min: Clic Aquí.

Etiquetas de Technorati: Error handlig SQL,Errores SQL,RAISERROR,sp_addmessage,sp_dropmessage

[SQL] ¿Por qué utilizar Tablas Temporales Locales, Globales o Variables de Tabla?.

Saludos amigos en esta ocasión leyendo me encontré con un tema muy interesante acerca de las tablas temporales en SQL, les dejo lo que traduje del tema con una explicación y ejemplo de como crear tablas temporales locales, globales y variables de tabla, dentro del Motor de Base de Datos de SQL Server 2008.

Tablas Temporales.

Las tablas temporales son consideradas tablas regulares, y estas se almacenan automáticamente en la base de datos de tempdb.

Las tablas temporales se pueden usar en los siguientes escenarios:

Como alternativa a los cursores: por ejemplo, en lugar de utilizar un cursor de Transact-SQL para recorrer un conjunto de resultados y realizar tareas basadas en cada fila, en su lugar puede utilizar una tabla temporal. Usando un bucle WHILE, puede recorrer cada fila de la tabla y realizar la acción de la fila especificada, posteriormente debe eliminar la fila de la tabla temporal.

Como almacenamiento incremental de conjuntos de resultados: Por ejemplo, imaginemos que tiene una sola consulta SELECT que realiza una combinación “JOIN” con diez tablas. A veces las consultas con varias combinaciones “JOINS” pueden funcionar de manera incorrecta. Una técnica para intentar es la de fraccionar o fragmentar una consulta grande en consultas más pequeñas. Si usamos tablas temporales, podemos crear conjuntos de resultados intermedios basados en consultas de menor tamaño, en lugar de intentar ejecutar una consulta única que sea demasiado grande y de combinaciones múltiples “multi-joined”.

Como temporal en la sobrecarga baja de búsqueda de tabla: Por ejemplo, imagine que usted está utilizando una consulta que tarda varios segundos en ejecutarse, pero sólo muestra un pequeño conjunto de resultados, el cual desea utilizar en varias áreas de su procedimiento almacenado, pero cada vez que se llama se incurre en el tiempo de ejecución de la consulta general. Para resolver esto, puede ejecutar la consulta una sola vez en el procedimiento, llenando una tabla temporal, de esta manera se puede hacer referencia a la tabla temporal en varios lugares en su código, sin incurrir en una sobrecarga de resultados adicional.

Existen dos tipos de tablas temporales: Globales y Locales.

Las tablas temporales Locales se escriben anteponiendo el símbolo # y tablas temporales Globales con el doble símbolo ##.

Tablas temporales Locales.

Las tablas temporales Locales están disponibles para usarse por cada conexión actual del usuario que los cree.

Varias conexiones pueden crear una tabla temporal con mismo nombre, esto solo para para tablas temporales Locales sin causar conflictos. La representación interna de la tabla local tiene un nombre único, para no estar en conflicto con otras tablas temporales con el mismo nombre creado por otras conexiones en la tempdb.

Las tablas temporales locales son eliminadas con el comando DROP o se eliminan automáticamente de memoria cuando se cierra la conexión del usuario.

Creando la tabla temporal Local:

CREATE TABLE #ProductosResumen
( idProducto int NOT NULL PRIMARY KEY,
  nombre varchar(75) NULL,
  precio smallmoney NULL
);

Insertando datos en la tabla temporal global.

INSERT #ProductosResumen (idProducto, nombre , precio)
SELECT id,nombre, precio FROM dbo.DEMO_PRODUCTO
ORDER BY nombre;

Seleccionando los datos de la tabla temporal.

--Seleccionando los datos de la tabla temporal
SELECT * FROM #ProductosResumen; 

-- Resumen de precios de la tabla temporal
SELECT AVG(precio) FROM #ProductosResumen;

 

Resultado.

Eliminando la tabla.

-- Eliminando la tabla temporal
DROP TABLE #ProductosResumen;

Nota: Para que el ejemplo funcione deben de ejecutar en un solo bloque de instrucciones todo el código que coloque anteriormente. Para este caso yo llene mi tabla temporal con datos de una tabla que ya tenia creada en la base de datos, ustedes pueden utilizar un insert normal.

Tablas Temporales Globales.

Las tablas temporales Globales tienen un alcance diferente al de las tablas temporales Locales. Una vez que una conexión crea una tabla temporal Global, cualquier usuario con permisos adecuados sobre la base de datos puede acceder a la tabla. A diferencia de tablas temporales Locales, no se pueden crear versiones simultáneas de una tabla temporal Global, ya que esto generará un conflicto de nombres.

Las tablas temporales Globales de eliminan explícitamente de SQL Server ejecutando DROP TABLE. También se eliminan automáticamente después de que se cierra la conexión que la creo, la tabla temporal Global no es referenciada por otras conexiones, pero es muy raro ver que se utilicen tablas temporales Globales en bases de datos en producción.

Es importante considerar cuando una tabla va o debe ser compartida a través de conexiones, se debe crear una tabla real, en lugar de una tabla temporal Global. No obstante, SQL Server ofrece esto como una opción.

Creando la tabla temporal Global:

--Creando la tabla temporal Global
CREATE TABLE ##Roles
( idRol int NOT NULL PRIMARY KEY,
  nombre varchar(30) NULL,
  activo bit NULL
);

Insertando y seleccionando datos de la tabla temporal global:

-- Insertando datos en la tabla temporal global
INSERT INTO ##Roles
VALUES(1,'Administrador',1),
      (2,'Supervisor',1),
      (3,'Programador',0)

-- Seleccionando los datos de la tabla temporal global
SELECT * FROM ##Roles;

Resultado:

 

Eliminando la tabla temporal global desde la conexión original que la creo.

-- Eliminando la tabla temporal global
DROP TABLE    ##Roles;

Variables de Tabla.

Por otro lado tenemos las Variables de Tabla que son un tipo de datos que puede ser utilizados en un lote Transact-SQL (Batch), procedimiento almacenado o función; estas variables de tabla son creado y definidas de forma similar a una tabla, sólo con un alcance de vida estrictamente definido. Las Variables de tabla suelen ser buenos reemplazos de tablas temporales siempre y cuando el conjunto de datos es pequeño.

Razones para usar las variables de tabla:

• Duración o alcance. La duración de la variable de tabla sólo vive durante la ejecución del lote, función, o procedimiento almacenado.
• Tiempos de bloqueo más cortos. Por el estrecho alcance o tiempo de vida.
• Menos re compilaciones cuando se usa en los procedimientos almacenados.

Como se menciono anteriormente, hay inconvenientes para utilizar las variables de tabla. El rendimiento de las variable de tabla se ve afectado cuando el resultado es demasiado grande o cuando los datos de la columna de cardinalidad son fundamentales para la optimización del proceso de consulta.

La sintaxis para crear una variable de tabla es similar a la de crear una tabla normal, se utiliza la palabra clave DECLARE y el nombre de tabla, anteponiendo el símbolo @:

DECLARE @TableName TABLE

(column_name <data_type> [ NULL | NOT NULL ] [ ,…n ] )

Creando una variable de tabla:

-- Creando la variable de tipo tabla.
DECLARE @EstatusUsuarios TABLE
( idEstatus int NOT NULL PRIMARY KEY,
  nombre varchar(30) NULL
)

Insertando y seleccionando datos de la variable tabla:

-- Insertando en la variable de tipo tabla.
INSERT INTO @EstatusUsuarios VALUES (1,'Activo'),
                                    (2,'Inactivo'),
                                    (3,'Bloqueado')

-- Consultando datos de la variable de tipo tabla.
SELECT * FROM @EstatusUsuarios;

Al terminar la ejecución del batch o bloque de instrucciones se eliminara la variable tabla, o si colocamos una instrucción GO automáticamente se eliminara y no la podremos utilizar como los muestro en el siguiente ejempló, recuerde que los ejemplo para claridad los coloque separados, pero los debe de ejecutar completos como lo hago abajo para el caso de la variables de tabla:

-- Creando la variable de tipo tabla.
DECLARE @EstatusUsuarios TABLE
( idEstatus int NOT NULL PRIMARY KEY,
  nombre varchar(30) NULL
)

-- Insertando en la variable de tipo tabla.
INSERT INTO @EstatusUsuarios VALUES (1,'Activo'),
                                    (2,'Inactivo'),
                                    (3,'Bloqueado')

-- Consultando datos de la variable de tipo tabla.
SELECT * FROM @EstatusUsuarios;

-- Al ejecutarse el go o el bloque de instrucciones
--la variable tabla se eliminara auitomaticamente.
Go

SELECT * FROM @EstatusUsuarios;

Como podrán notar en el primer select nos regreso:

Y para el segundo select:

Esto es por que como lo comente anteriormente el tiempo de vida de la variable tabla termina al ejecutarse el bloque de instrucciones, en este caso termino con el GO.

Cuando encontremos problemas de rendimiento, debemos de asegurarnos de probar todas las soluciones y alternativas, y no necesariamente asumir que una de las opciones (tablas Temporales) son menos deseables que otras (variables de tabla).

Espero les sea de ayuda, se que esta un poco largo el Post, pero creo vale la pena leerlo.

 

Apóyame votando aquí: http://blogit.ms/TopBloggers.aspx en la categoría SQL Server. Los pasos para votar están aquí te llevara 3 min: Clic Aquí.

Etiquetas de Technorati: Tablas temporales,temporary tables,Local Tables,Global Tables,Variables de Tabla,SQL 2008

¿Cómo votar por este Blog?

Amigos, en esta ocasión les pido su apoyo para contar con su invaluable voto, es la primera vez que concurso en Blog IT, como sabrán es una competencia de blogs con diferentes categorías, yo estoy participando en la de SQL Server, para hacer esto más breve espero contar con su voto, les dejo los pasos para que lo puedan hacer, me despido enviándoles  un cordial saludo.

Nota: En ocasiones el sitio de votación esta muy lento, por favor se paciente y apóyame es importante para mi contar con tu voto.

Paso 1. Ir al siguiente enlace: http://www.blogit.ms/TopBloggers.aspx o hacer clic en el siguiente botón:

Paso 2. Seleccione la categoría de SQL Server y haga clic en el botón buscar.

Nota: No se a que se deba pero se tarda unos instantes en cargar la página, por favor sea paciente y espere un poco :D.

Paso 3. Busque mi blog https://sqlpsykrest.wordpress.com y de clic en el botón Votar.

Paso 4. Ingrese su correo electrónico.

Paso 5. Confirme el voto. Le llegara un correo electrónico en el cual se le pedirá que haga clic en un enlace para confirmar el voto. Nota: el correo muchas veces llega al SPAM o correo no deseado.

Listo amigos con esto su voto quedara contabilizado, el tiempo estimado es de 3 min, por favor apóyenme, sin duda espero contar con ustedes.

[SQL] Previniendo la Inyección de código.

Saludos amigos, encontré un ejemplo de una función escalar “Scalar User-Defined Functions”, por @Joseph Sack, la cual verifica si una instrucción TSQL esta intentando ejecutar código malintencionado que puede afectar de manera catastrófica la información y estructura de nuestras bases de datos. Como muchos de ustedes sabrán al tener sentencias TSQL no parametrizadas corremos un enorme riesgo de sufrir ataques de inyección, el script que a continuación les comparto verifica que la sentencia a ejecutar no contenga código que intente alterar los datos o la estructura de los mismos.

1. Creamos una tabla llamada DEMO_ROLES, a la cual insertaremos datos a través de Sentencias dinámicas SQL.

Creando la tabla:

USE prueba;
GO
 
CREATE TABLE dbo.DEMO_ROLES(
    id_rol bigint IDENTITY(1,1) NOT NULL,
    rol varchar(50) NOT NULL,
    CONSTRAINT PK_DEMO_ROLES PRIMARY KEY CLUSTERED 
    ([id_rol] ASC)
);
GO

Insertando datos:

INSERT INTO dbo.DEMO_ROLES 
VALUES ('Administrador'),
       ('Gerente'),
       ('Supervisor'),
       ('Desarrollador');

Seleccionando datos con “sp_executesql”.

DECLARE @SQLtext nvarchar(350);
SET @SQLtext = N'SELECT * FROM dbo.DEMO_ROLES';
EXEC sp_executesql @SQLtext;
GO

Resultado:

2. Simulando la Inyección de código. Para este paso simularemos que la cadena de código que se va a ejecutar fue modificada malintencionadamente y le concatenaron (Inyectaron) una sentencia que borra la tabla DEMO_ROLES.

Ejecutando la sentencia:

DECLARE @SQLtext nvarchar(350);
SET @SQLtext = N'SELECT * FROM dbo.DEMO_ROLES' + ';DROP TABLE dbo.DEMO_ROLES';
EXEC sp_executesql @SQLtext;
GO

Nota: Por el orden en el que se ejecutan las sentencias, primero se seleccionan los datos y después se elimina la tabla, por lo tanto hasta la próxima ejecución de las sentencias se producirá una excepción donde el SQL nos indicara que la tabla DEMO_ROLES no existe, esto a cause de que fue eliminada por la simulación de la inyección de código.

Resultado 1 (Primera ejecución con inyección simulada):

Resultado 2 (Ejecuciones posteriores a la inyección simulada):

Ahora como lo resolvemos:

1. Para esto tenemos que crear la tabla DEMO_ROLES, e insertar los datos (vea la parte inicial del Post).

2. Crear una función escalar la cual se encargara de verificar si las sentencias a ejecutar son maliciosas y contienen código que pueda alterar nuestra base de datos.

USE prueba;
GO

CREATE FUNCTION [dbo].[udf_CheckForSQLInjection]
    (@TSQLString varchar(max))
RETURNS BIT
AS
BEGIN
DECLARE @IsSuspect bit
-- UDF assumes string will be left padded with a single space
SET @TSQLString = ' ' + @TSQLString
    IF (PATINDEX('% xp_%' , @TSQLString ) <> 0 OR
        PATINDEX('% sp_%' , @TSQLString ) <> 0 OR
        PATINDEX('% DROP %' , @TSQLString ) <> 0 OR
        PATINDEX('% GO %' , @TSQLString ) <> 0 OR
        PATINDEX('% INSERT %' , @TSQLString ) <> 0 OR
        PATINDEX('% UPDATE %' , @TSQLString ) <> 0 OR
        PATINDEX('% DBCC %' , @TSQLString ) <> 0 OR
        PATINDEX('% SHUTDOWN %' , @TSQLString )<> 0 OR
        PATINDEX('% ALTER %' , @TSQLString )<> 0 OR
        PATINDEX('% CREATE %' , @TSQLString ) <> 0OR
        PATINDEX('%;%' , @TSQLString )<> 0 OR
        PATINDEX('% EXECUTE %' , @TSQLString )<> 0 OR
        PATINDEX('% BREAK %' , @TSQLString )<> 0 OR
        PATINDEX('% BEGIN %' , @TSQLString )<> 0 OR
        PATINDEX('% CHECKPOINT %' , @TSQLString )<> 0 OR
        PATINDEX('% BREAK %' , @TSQLString )<> 0 OR
        PATINDEX('% COMMIT %' , @TSQLString )<> 0 OR
        PATINDEX('% TRANSACTION %' , @TSQLString )<> 0 OR
        PATINDEX('% CURSOR %' , @TSQLString )<> 0 OR
        PATINDEX('% GRANT %' , @TSQLString )<> 0 OR
        PATINDEX('% DENY %' , @TSQLString )<> 0 OR
        PATINDEX('% ESCAPE %' , @TSQLString )<> 0 OR
        PATINDEX('% WHILE %' , @TSQLString )<> 0 OR
        PATINDEX('% OPENDATASOURCE %' , @TSQLString )<> 0 OR
        PATINDEX('% OPENQUERY %' , @TSQLString )<> 0 OR
        PATINDEX('% OPENROWSET %' , @TSQLString )<> 0 OR
        PATINDEX('% EXEC %' , @TSQLString )<> 0)
    BEGIN
        SELECT @IsSuspect = 1
    END
    ELSE
    BEGIN
        SELECT @IsSuspect = 0
    END

RETURN (@IsSuspect)
END;
GO

3. Ejecutando las sentencias TSQL con la ayuda de la función para verificar la inyección de código.

DECLARE @SQLtext nvarchar(350);
SET @SQLtext = N'SELECT * FROM dbo.DEMO_ROLES' + ';DROP TABLE dbo.DEMO_ROLES';

IF dbo.udf_CheckForSQLInjection(@SQLtext) = 1
BEGIN
    PRINT ('Advertencia: El código es sospecho de contener código malicioso (TSQL Inyection).')
END
ELSE
BEGIN
    EXEC sp_executesql @SQLtext;
END

Resultado 1 con inyección de código:

Resultado 2 sin inyección de código:

Pueden hacer la modificaciones que crean necesarias a la función de inyección de código, ya que el funcionamiento y validación depende de las necesidades que deseemos cubrir.

Bueno amigos me despido y con gusto espero comentarios, por favor apóyenme votando por mi blog en http://blogit.ms en la categoría de SQL Server.

Etiquetas de Technorati: TSQL Injection,Inyección de código SQL,SQL