Script Inside

Entradas

Mostrando las entradas etiquetadas como T-SQL

SSMS ya tiene su propia ruta de actualizaciones

SQL Server 2016 ya está en la fase 2 de beta técnica y ha sido lanzado al público con una licencia de prueba de 180 días y cargado de novedades. Una de las más importantes, y sobre la que hablaré en este post, es la nueva ruta de actualizaciones que recibe SQL Server Management Studio (SSMS). Era ya muy necesario el proceso de modernización de SQL Server Management Studio como la principal aplicación / herramienta de gestión para interactuar con la plataforma insignia de base de datos de Microsoft, se había quedado a la zaga del resto de avances en el producto. Desde el año 2005 cuando se libera SSMS como la evolución de SQL Server Enterprise Manager solamente parecía actualizarse con el fin de proporcionar todas las funcionalidades añadidas como características a SQL Server. Finalmente comienza a parecer que el equipo de ingeniería de SQL Server ha hecho caso y ha comenzado a aumentar, no sólo los recursos y el personal de desarrollo necesarios, sino también proporcionar una ma...

Consultas útiles SQL Server

Muchas veces necesitamos conocer las tablas, campos, claves, etc. de una base de datos en concreto, todos estos datos están disponibles en tablas de sistema de SQL Server, veamos algunos ejemplos. Devolver las tablas de la base de datos actual con sus claves primarias SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' Devolver los campos de una tabla concreta que son claves foráneas y la tabla a la que hace referencia SELECT CU.COLUMN_NAME, PK.TABLE_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME WHERE FK.TABLE_NAME = ? Devo...

Trabajar con Excel en SQL Server

agosto 14, 2014

Muchas veces necesitamos obtener datos desde un Excel e importarlos en una tabla de nuestra base de datos o sencillamente utilizarlos con alguna consulta, si este es tu caso este tutorial te puede ayudar a trabajar con archivos Excel como si fueran una base de datos y cada una de sus hojas como una tabla. Lo primero que tienes que tener instado en el servidor donde está SQL Server (no vale en la máquina remota donde haces las consultas) es el Office o en su defecto 2007 Office System Driver Data Connectivity Components (solo 32 bits) o Microsoft Access Database Engine 2010 Redistributable (versiones de 32 y 64 bits). Es muy muy importante tener esto instalado para evitar el siguiente error The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered . Más importante aún es tener la versión correcta, si tu SQL Server es de 64 bits solo puedes instalar de 64 bits, si es de 32 bits lo mismo, en caso contrario tendrías el siguiente error OLE DB provider 'Micr...

PsExec y SQL Server

Microsoft tiene un kit de recursos con varias herramientas para la línea de comandos que permiten realizar administrar sistemas remotos y locales . Este grupo de herramientas es conocido como PsTools y entre ella está Psexec , una herramienta para la línea de comandos que permite ejecutar procesos en equipos remotos. ¿Como ejecutar psexec en un trigger de SQL Server? En realidad esto es válido para cualquier aplicación local. SET @cadena='C:\ruta_completa\psexec.exe \\ip_remota\ -d "C:\ruta_archivo_bat.bat" var1±'+@var1+'~var2±'+@var2+', NO_OUTPUT' Con el comando anterior especificamos la ruta del archivo local ejecutable y la el proceso bat que queremos ejecutar en remoto, las variables 1 y 2 son opcionales en caso de que vuestro proceso bat las necesite. Con el siguiente comando ejecutamos la instrucción almacenada en la variable anterior exec master..xp_cmdshell @cadena Recordar que el usuario que ejecuta los procesos de SQL Server debe de...

Optimizando consultas en SQL Server

SQL Server Managment Studio es, y no me cansaré de decirlo, la mejor herramienta para gestión de bases de datos que existe, si MySQL tuviese algo similar sería genial. Una de las cosas que podemos hacer con SQL Server Managment Studio es optimizar y analizar nuestros índices. Como diría el mismo Microsoft: SQL Server Management Studio es una herramienta gráfica interactiva que permite a un administrador o a un programador de bases de datos escribir consultas, ejecutar simultáneamente varias consultas, ver los resultados, analizar el plan de consultas y recibir asistencia para mejorar el rendimiento de las consultas. Optimizando consultas en SQL Server Una de las opciones en SQL Server es ejecutar una consulta y ver su plan de ejecución ¿Qué es un plan de ejecución? Las opciones del Plan de ejecución muestran gráficamente los métodos de recuperación de datos elegidos por el optimizador de consultas de SQL Server . El plan de ejecución gráfico utiliza iconos para r...

SQL Server vs MySQL

Esto no es una comparativa a nivel profundo ni lo pretende ser, mi único objetivo es remarcar desde el punto de vista de un usuario básico las ventajas de uno u otro sistema a la hora de trabajar con ellos. No a la hora de instalarlos, configurarlos ni mantenerlos, solo a la hora de pedir datos almacenados en estos gestores de bases de datos. Por mi trabajo tengo que manejar ambos sistemas a partes iguales y muchas veces extraño cosas de uno en el otro y viceversa, aquí os dejo una pequeña comparativa desde mi punto de vista y basada en mi experiencia profesional, si alguien descubre algún error o una mejor forma de hacer las cosas estaría encantado de recibir un feedback. Cosas que agradezco de MySQL Me encantan las sintaxis ON DUPLICATE KEY e INSERT IGNORE, me parecen muy útiles a la hora de ejecutar consultas y gestionar errores. El uso de los índices de texto completo me parecen mucho más sencillo e intuitivo sobre todo con la integración de estos en tablas InnoDB desde las...

Almacenar IPs en las bases de datos

La mayoría de los usuarios cuando tienen que almacenar IPs en sus bases de datos los hacen con tipos de campos VARCHAR cuando es mucho más óptimo almacenarlos con un entero sin signo (en caso de MySQL) o BIG INT (en caso de SQL Server). Convertir IPs en MySQL Existen 2 funciones para ello inet_aton e inet_ntoa , la primera convierte la IP en un número y la segunda es el proceso contrario. Convertir IPs en SQL Server En SQL Server no hay funciones predefinidas para ello pero las podemos crear muy fácilmente, en el siguiente enlace hay un par de ejemplos muy buenos para convertir IPs a BIG INT en SQL Server y viceversa . Esta transformación también la podemos hacer al insertar los datos en el lenguaje de programación, en el caso de PHP sería ip2long y long2ip .

SQL Server default values

diciembre 30, 2013

Si alguna vez habéis querido insertar en una tabla solo los valores por defecto , en SQL es muy fácil hacerlo con el siguiente comando: INSERT INTO tabla DEFAULT VALUES Para ver las opciones del comando insert en la web de Microsoft hay una información muy bien detallada.

SQL Server OUTPUT

diciembre 30, 2013

El comando OUTPUT es uno de los más interesantes y útiles en SQL Server, se utiliza para devolver los datos de las filas insertadas, actualizadas o eliminadas. Por ejemplo, para obtener el id de una nueva fila insertada: INSERT INTO tabla (campos) OUTPUT inserted.tabla_id VALUES (valores) La tabla inserted es una tabla temporal que almacena los datos de la fila insertada incluyendo en nuevo ID. Al actualizar debemos utilizar esta misma palabra clave y lo que devuelve son los datos de la fila al modificarse. O sea al insertar u actualizar obtenemos los datos nuevos y al eliminar obtendremos los antiguos usando el comando deleted. Sin dudas el uso más frecuente es obtener la clave única al insertar datos en una tabla sin necesidad de ejecutar una consulta adicional .

Tips para campos datetime en SQL Server

octubre 30, 2013

Para mi es mucho más sencillo e intuitivo trabajar con campos datetime en MySQL pero SQL Server ofrece las mismas (sino alguna más) posibilidades de convertir estos campos en formatos concretos de hora y fecha. Para ello tenemos las funciones CONVERT y DATEPART . Aquí os dejo algunos ejemplos de su uso. Convertir un campo datetime a fecha en castellano (el año con 4 dígitos) SELECT CONVERT(VARCHAR(10), CURRENT_TIMESTAMP, 103); Convertir un campo datetime a fecha en castellano (el año con 2 dígitos) SELECT CONVERT(VARCHAR(8), CURRENT_TIMESTAMP, 3); Extraer la fecha de un campo datetime SELECT CONVERT(DATE, CURRENT_TIMESTAMP); Extraer el tiempo de un campo datetime con milisegundos SELECT CONVERT(TIME, CURRENT_TIMESTAMP); Extraer el tiempo de un campo datetime sin milisegundos SELECT CONVERT(VARCHAR(9), CURRENT_TIMESTAMP, 108); ¿Se os ocurre algún otro formato más?

SQL Server 2008 registro de transacciones

El registro de transacciones en SQL Server es el archivo que registra como bien dice su nombre todas las transacciones de una tabla y sus modificaciones en la base de datos y si no le ponemos un límite al mismo este crecerá de manera indefinida llegando incluso a colapsar todo el espacio libre de nuestro almacenamiento. La recomendación de Microsoft es la siguiente: El registro de transacciones se debe truncar periódicamente para evitar que se llene. Sin embargo, algunos factores pueden retrasar el truncamiento del registro, por lo que es importante supervisar el tamaño del registro. Algunas operaciones se pueden registrar mínimamente para reducir su impacto sobre el tamaño del registro de transacciones. ¿Cómo saber el espacio ocupado por cada registro de transacciones? Ejecutamos la siguiente consulta: DBCC SQLPERF(LOGSPACE); Esto nos devolverá una tabla con los siguientes campos: nombre de la base de datos, tamaño del log en MB, espacio del log utilizado en porcentaje y el est...

Transacciones MySQLi vs MSSQL

febrero 22, 2013

Para los que estamos acostumbrados a trabajar con SQL Server, el manejo de transacciones es bastante transparente y sencillo y se apoya de 3 funciones sqlsrv_begin_transaction, sqlsrv_rollback y sqlsrv_commit. ¿Qué quiere decir esto? Pues que u inicias una transacción cuando quieres y cuando la terminas o la cancelas el sistema vuelve a su estado incial. ¿Cómo gestiona las transacciones MySQL? Pues también utiliza 3 funciones autocommit, rollback y commit. ¿Cuál es la diferencia? Pues que para iniciar transacciones utilizando MySQLi tienes que ejecutar autocommit(false) lo cual modifica el estado global de esa conexión, con lo cual al cancelar (rollback) o ejecutar (commit) una transaccción el sistema no vuelve a su estado inicial sino hasta que ejecutas autocommit(true). Esto es un verdadero engorro, vemos un ejemplo que hace lo mismo en ambos sistemas. Transacciones con MSSQL sqlsrv_begin_transaction($conexion) if(bien) sqlsrv_commit($conexion) else s...

Introducción a las fechas en SQL Server

En este post quiero tratar de explicar cómo usar las fechas en SQL Server y que cosas debemos tomar en consideración pero antes voy a tratar de explicar que son para SQL Server, el cual, tiene básicamente dos tipos de datos donde se pueden almacenar las mismas: Datetime y SmallDateTime, en este cuadro veremos las diferencias entre estos dos tipos de datos. DateTime Valores de Fecha y Hora que están comprendidos entre 1/1/1763 y 31/12/9999. La hora se expresa con una exactitud de hasta 1/300 de segundo SamallDateTime Valores de Fecha y Hora que están comprendidos entre 1/1/1900 y 6/6/2079. El grado de precisión de la hora llega hasta el minuto ¿Cómo guarda internamente SQL Server las fechas ? El motor de SQL Server siempre guarda las fechas de una sola forma y no esta referida a ningún formato concreto como podría ser Americano, Español, Japonés, etc.; SQL Server guarda las fechas DateTime como enteros de 4 bytes (los primeros 4 bytes almacenan la fecha y los otros 4 la...

Procedimientos vs T-SQL en SQL Server

¿Por qué recomendar el uso de los procedimientos almacenados en lugar de las sentencias T-SQL ? Los procedimientos almacenados deben de ser una de las herramientas reinas de los motores de bases de datos (cómo es el caso de SQL Server) y de los DBA ¿Por qué? Mayor optimización para el motor : Este mismo no debe compilar (a menos que se lo indiquemos) cada instrucción T-SQL, sino que ya la tiene compilada y esto genera mucho mayor rendimiento. También se da el caso que el procedimiento reside en nuestro servidor por lo cual el trafico de red es muy inferior al que podemos hacer si mandamos las T-SQL desde nuestro cliente. Seguridad : Los procedimientos no solo son buenos por rendimiento, sino que nos permiten hacer las operaciones no directamente sobre nuestras tablas. Reutilización de código : El uso de procedimientos es muy útil para esto, si la consulta de rangos de fechas para buscar los pedidos en firme de un cliente por ejemplo, la necesitamos en mas de una aplicación, e...

SQL Server: acceso exclusivo a la base de datos

noviembre 11, 2011

Muchas de las operaciones que se realizan sobre las bases de datos de SQL Server requieren el uso exclusivo de esta base de datos por parte del usuario que está ejecutando el proceso o la tarea, una de ella puede ser la restauración de la misma desde un backup. Por ser un requerimiento común es muy normal recibir este mensaje de error: Exclusive access could not be obtained because the database is in use Corregirlo no suele ser muy sencillo, más bien todo lo contrario y depende de los accesos que tenga esa base de datos, aquí os dejaré algunos pasos para identificar e intentar garantizar ese acceso exclusivo . Lo primero que debemos hacer es terminar todas las conexiones existentes a la base de datos y ello lo haremos con la siguiente instrucción: USE database GO ALTER DATABASE database SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO ALTER DATABASE database SET MULTI_USER GO También podemos ejecutar esta opción utilizando el procedimiento almacenado sp_dboption: Use master go sp_db...

SQL Server: listados de bases de datos y tablas

noviembre 09, 2011

Para saber cuales son todas las bases de datos de un servidor SQL Server tenemos estas dos opciones: EXEC sp_helpdb SELECT * FROM master.dbo.sysdatabases; Ambas nos devuelven el mismo listado lo único que cambia son los datos que nos muestran, la consulta SQL es mucho más amplia en datos que el procedimiento almacenado. Importante: ambas deben de ser ejecutadas bajo master: USE master GO. Para saber cuales son todas las tablas de una base de datos en SQL Server lo primero que debes hacer es tener abierta la ventana de consulta bajo tu base de datos (USE DB_name) y luego ejecutar una de estas dos opciones: EXEC sp_tables SELECT * FROM information_schema.tables; Y por último para saber todas las columnas de una tabla en SQL Server puedes ejecutar el procedimiento almacenado sp_columns. Las opciones son ilimitadas y ahora también existe la posibilidad de generar un solo archivo SQL que puede extraer todo lo que necesitas incluso extraer aparte de todas las estructuras, todo los ...

SQL Server: reorganizar y volver a generar índices

noviembre 07, 2011

Según la propia Microsoft : El Motor de base de datos de SQL Server mantiene índices automáticamente cada vez que inserta, actualiza o elimina operaciones realizadas en los datos subyacentes. Con el tiempo, estas modificaciones pueden hacer que la información del índice se disperse por la base de datos (se fragmente). La fragmentación ocurre cuando los índices tienen páginas en las que la ordenación lógica, basada en el valor de clave, no coincide con la ordenación física dentro del archivo de datos. Los índices muy fragmentados pueden reducir el rendimiento de la consulta y ralentizar la respuesta de la aplicación. Se puede solucionar la fragmentación del índice reorganizándolo o volviéndolo a generar pero el primer paso es identificar los índices fragmentados, para ello podemos ejecutar la siguiente consulta SQL que nos sacará un listado de los índices, las tablas a las que pertenecen y el nivel de fragmentación de los mismos: SELECT a.object_id as table_id, OBJECT_NAME(a.object...

Preparar y ejecutar consultas con SQL Server driver para PHP

noviembre 02, 2011

Los que acostumbramos a utilizar MySQL no estamos familiarizados con esta sintaxis de preparar y ejecutar consultas que tiene el driver de Microsoft para PHP. En este post intento mostrar cómo usar el driver de SQL Server para PHP para ejecutar una instrucción preparada varias veces. Esto es mediante la combinación de sqlsrv_prepare y sqlsrv_execute , y el resultado es una instrucción que se compila una vez en el servidor y se puede ejecutar después varias veces con distintos valores de parámetro. En el caso de consultas únicas, se recomienda el uso de sqlsrv_query que es a lo que estamos acostumbrados en MySQL. Al preparar una instrucción que usa variables como parámetros, las variables se enlazan a la instrucción. Eso significa que si se actualizan los valores de las variables, la próxima vez que se ejecute la instrucción se ejecutará con los valores de parámetros actualizados. En el siguiente enlace tenemos un ejemplo de cómo utilizar estas funciones para ejecutar una consul...

Errores que no existen en SQL Server

octubre 20, 2011

Esto no sé si es un bug o que no he logrado dar realmente con la información correcta para solucionar mi problema, pero a falta de una solución propongo el problema. Hace poco me estaba dando un error bastante frecuente el SQL Server, error del tipo Communication link failure y número 16389. Al ir al agente de SQL Server e intentar crear una alerta para este error en concreto (16389) (ver imagen siguiente para los pasos que he seguido al crear la alerta nueva) Pues eso que al intentar crear la alerta como os he mostrado anteriormente me sale el siguiente error: No se puede crear una nueva alerta . (SqlManagerUI) Error de Crear para Alerta 'Error'. (Microsoft.SqlServer.Smo) Excepción al ejecutar una instrucción o un proceso por lotes Transact-SQL. (Microsoft.SqlServer.ConnectionInfo) The specified @message_id ('16389') does not exist. (Microsoft SQL Server, Error: 14262) ¡Que raro! un error reportado por el propio SQL Server y que no exista, bueno, vamos a com...

Restablecer valores de la columna identidad en SQL Server

octubre 19, 2011

Hace un tiempo hablé en un post sobre los errores que daba SQL Server al intentar truncar una tabla con claves extranjeras (foreign keys) aunque estas estuviesen vacías. Uno de los puntos que traté en ese post era restablecer el valor de la clave principal de la tabla a 0 y el método que utilicé era el mismo que nos explican en este post Reset Identity Column Value in SQL Server . Ayer casualmente probando este método en una tabla recién truncada descubrí que cuando se realiza la opción TRUNCATE TABLE el valor de la columna de identidad (clave principal) toma el valor NULL en vez del valor 0. Lo descubrí al hacer lo siguiente : DBCC CHECKIDENT (tabla, RESEED, 0) La respuesta del SQL Server Managment Studio fue la siguiente: Checking identity information: current identity value 'NULL', current column value '0'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. En realidad esto no afecta en nada es sólo una curiosidad m...