sys.dm_os_performance_counters

La performance de un servidor SQL Server 2005 puede ser monitoreada utilizando los contadores de performance. Para lo cual podemos usar el System Monitor (perfmon) o, a partir de SQL Server 2005, utilizando la Dynamic Management View (DMV a partir de ahora) con sys.os_exec_performance_counters.

Algunos contadores útiles (en otro posteo indicaré como analizar estos contadores):
SQLServer:Buffer Partition
SQLServer:User Settable
SQLServer:Databases
SQLServer:CLR
SQLServer:Cursor Manager by Type
SQLServer:Exec Statistics
SQLServer:Transactions
SQLServer:Memory Manager
SQLServer:SQL Errors
SQLServer:Buffer Node
SQLServer:Plan Cache
SQLServer:Access Methods
SQLServer:Cursor Manager Total
SQLServer:Broker Activation
SQLServer:Latches
SQLServer:Wait Statistics
SQLServer:Broker/DBM Transport
SQLServer:General Statistics
SQLServer:SQL Statistics
SQLServer:Catalog Metadata
SQLServer:Broker Statistics
SQLServer:Locks
SQLServer:Buffer Manage
En SQL Server 2000 podíamos obtener la información desde la tabla master.dbo.sysperfinfo. En 2005 se nos provee con una vista que representa esta tabla, solo a los efectos de mantener compatibilidad con codificaciones previas, pero en 2005 usted debiera usar el DMV “sys.os_exec_performance_counters”.

Para Finalizar un ejemplo:
Vamos a crear un script para calcular el "buffer cache hit ratio", mientras mas cercano a 100% mejor el valor, ya que indicaría que el buffer cache está siendo utilizado de manera óptima y que las páginas permanecen en el cache de buffer. Y por ende la performance de su servidor será mejor.
Create Proc dbo.P_ContadorBufferCacheHit
as
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 [BufferCacheHitRatio]
FROM (SELECT *, 1 x FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio'
AND object_name = 'SQLServer:Buffer Manager') a
JOIN
(SELECT *, 1 x FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'
AND object_name = 'SQLServer:Buffer Manager') b
GO

Hugo Román Bernachea
Mail de contacto: SQLServer777@gmail.com

Microsoft Certified DBA
Microsoft Certified Trainer
Twitter: @bernachea


Read More...

Un Stored para ejecutar en modo DAC (Conexión Administrativa Dedicada)

Como todos bien sabemos, en SQL Server 2005 disponemos de las DAC, Conexiones administrativas dedicadas, para ejecutar distintos tipos de tareas en caso de encontrarnos con fallos o problemas en nuestros servidores SQL Server 2005.

La pregunta es, que es lo que podríamos ejecutar para tener un vistazo general de los problemas de los servidores?

Googleando por ahí encontré el siguiente Stored que me parece muy util de tener creado como para poder ejecutar en modo DAC y obtener la información que estamos buscando.

Script---->

USE master
GO

-- Este Stored nos dará la info de los servidores en cuestión.
-- Conectar en modo DAC y ejecutar el Stored que YA existirá de ANTEMANO en la base

CREATE PROC dbo.p_Info_Servidor
AS

SELECT '*** comienzo de informe DAC ***'

SELECT '-- Mostrar SQL Server Info'
EXEC ('USE MASTER')

SELECT
CONVERT(char(20), SERVERPROPERTY('MachineName')) AS 'Nombre Maquina',
CONVERT(char(20), SERVERPROPERTY('ServerName')) AS 'Nombre SQL Server',

(CASE WHEN CONVERT(char(20), SERVERPROPERTY('InstanceName')) IS NULL
THEN 'Instancia Predeterminada'
ELSE CONVERT(char(20), SERVERPROPERTY('InstanceName'))
END) AS 'Nombre de Instancia',

CONVERT(char(20), SERVERPROPERTY('EDITION')) AS Edicion,
CONVERT(char(20), SERVERPROPERTY('ProductVersion')) AS 'Version',
CONVERT(char(20), SERVERPROPERTY('ProductLevel')) AS 'Level',

(CASE WHEN CONVERT(char(20), SERVERPROPERTY('ISClustered')) = 1
THEN 'Clustered'
WHEN CONVERT(char(20), SERVERPROPERTY('ISClustered')) = 0
THEN 'NOT Clustered'
ELSE 'INVALID INPUT/ERROR'
END) AS 'FAILOVER CLUSTERED',

(CASE WHEN CONVERT(char(20), SERVERPROPERTY('ISIntegratedSecurityOnly')) = 1
THEN 'Seguridad Integrada '
WHEN CONVERT(char(20), SERVERPROPERTY('ISIntegratedSecurityOnly')) = 0
THEN 'Seguridad SQL Server '
ELSE 'INVALID INPUT/ERROR'
END) AS 'SECURITY',

(CASE WHEN CONVERT(char(20), SERVERPROPERTY('ISSingleUser')) = 1
THEN 'Single User'
WHEN CONVERT(char(20), SERVERPROPERTY('ISSingleUser')) = 0
THEN 'Multi User'
ELSE 'INVALID INPUT/ERROR'
END) AS 'USER MODE',

CONVERT(char(30), SERVERPROPERTY('COLLATION')) AS COLLATION

SELECT '-- Mostrar las 5 sentencias mas consumidoras'
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;

SELECT '-- Mostrar quienes están logeados'
SELECT login_name ,COUNT(session_id) AS session_count
FROM sys.dm_exec_sessions
GROUP BY login_name;

SELECT '-- Mostrar cursores con tiempos extensos de ejecución'
EXEC ('USE master')

SELECT creation_time ,cursor_id
,name ,c.session_id ,login_name
FROM sys.dm_exec_cursors(0) AS c
JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id
WHERE DATEDIFF(mi, c.creation_time, GETDATE()) > 5;

SELECT '-- Mostrar sesiones con transacciones abiertas'
SELECT s.*
FROM sys.dm_exec_sessions AS s
WHERE EXISTS
(
SELECT *
FROM sys.dm_tran_session_transactions AS t
WHERE t.session_id = s.session_id
)
AND NOT EXISTS
(
SELECT *
FROM sys.dm_exec_requests AS r
WHERE r.session_id = s.session_id
);

SELECT '-- Mostrar espacio libre en tempdb '
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

SELECT '-- Mostrar espacio ocupado por tempdb'
SELECT SUM(size)*1.0/128 AS [size in MB]
FROM tempdb.sys.database_files

SELECT '-- Mostrar jobs activos'
SELECT DB_NAME(database_id) AS [Database], COUNT(*) AS [Active Async Jobs]
FROM sys.dm_exec_background_job_queue
WHERE in_progress = 1
GROUP BY database_id;

SELECT '--Mostrar clientes conectados'
SELECT session_id, client_net_address, client_tcp_port
FROM sys.dm_exec_connections;

SELECT '--Mostrar batchs en ejecución'
SELECT * FROM sys.dm_exec_requests;

SELECT '--Mostrar request actualmente bloqueados'
SELECT session_id ,status ,blocking_session_id
,wait_type ,wait_time ,wait_resource
,transaction_id
FROM sys.dm_exec_requests
WHERE status = N'suspended'

SELECT '--Mostrar fechas de ultimos backups ' as ' '
SELECT B.name as Database_Name,
ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(Backup_finish_date)))), 'NEVER')
as DaysSinceLastBackup,
ISNULL(Convert(char(10),
MAX(backup_finish_date), 101), 'NEVER')
as LastBackupDate

FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A
ON A.database_name = B.name AND A.type = 'D' GROUP BY B.Name ORDER BY B.name

SELECT '--Mostrar jobs que están todavía en ejecución' as ' '
exec msdb.dbo.sp_get_composite_job_info NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL

SELECT '--Mostrar informe de Jobs fallidos ' as ' '
SELECT name FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B WHERE A.job_id = B.job_id AND B.last_run_outcome = 0

SELECT '--Mostrar jobs deshabilitados ' as ' '
SELECT name FROM msdb.dbo.sysjobs WHERE enabled = 0 ORDER BY name

SELECT '--Mostrar espacio disponible de BD ' as ' '
exec sp_MSForEachDB 'Use ? SELECT name AS ''Name of File'', size/128.0 -CAST(FILEPROPERTY(name, ''SpaceUsed'' )
AS int)/128.0 AS ''Espacio disponible en MB'' FROM .SYSFILES'

SELECT '--Mostrar total DB size (.MDF+.LDF)' as ' '
set nocount on
declare @name sysname
declare @SQL nvarchar(600)
-- Use temporary table to sum up database size w/o using group by
create table #databases (
DATABASE_NAME sysname NOT NULL,
size int NOT NULL)
declare c1 cursor for
select name from master.dbo.sysdatabases
-- where has_dbaccess(name) = 1 -- Only look at databases to which we have access
open c1
fetch c1 into @name

while @@fetch_status >= 0
begin
select @SQL = 'insert into #databases
select N'''+ @name + ''', sum(size) from '
+ QuoteName(@name) + '.dbo.sysfiles'
-- Insert row for each database
execute (@SQL)
fetch c1 into @name
end
deallocate c1

select DATABASE_NAME, DATABASE_SIZE_MB = size*8/1000 -- Convert from 8192 byte pages to K and then convert to MB
from #databases order by 1

select SUM(size*8/1000)as '--Shows disk space used - ALL DBs - MB ' from #databases

drop table #databases

SELECT '--Mostrar espacio disponible en disco ' as ' '
EXEC master..xp_fixeddrives

SELECT '*** Fin de informes **** '

GO

A este stored le pueden agregar las sentencias que crean convenientes, pero pueden tomar este script como base.

Hugo Román Bernachea
Mail de contacto: SQLServer777@gmail.com

Microsoft Certified DBA
Microsoft Certified Trainer
Twitter: @bernachea

Read More...

Como determinar índices faltantes en SQL Server 2005??

En SQL Server 2005 existen unas nuevas vistas dinámicas que nos facilitan el proceso de determinar que índices optimizarían el rendimiento de nuestras consultas:

sys.dm_db_missing_index_group_stats Regresa información acerca de grupos de índices no existentes, por ejemplo, la performance que se podría obtener implementando un grupo específico de índices.
sys.dm_db_missing_index_groups Regresar información acerca de un grupo específico de indices no declarados, como el identificador de grupo y el identificador de todos los índices que están contenidos en dicho grupo.
sys.dm_db_missing_index_details Devuelve información detallada acerca de un posible índice a ser creado, por ejemplo nombre e identificador de la tabla donde el índice podría ser creado y las columnas y tipos que conformarían dicho índice.
sys.dm_db_missing_index_columns Devuelve info acerca de los campos que podrían conformar un índice.that are missing an index.

Cada vez que SQL ejecuta una consulta, internamente determina si esa consulta podía haber sido optimizada con el uso de algún índice inexistente al momento del query (por eso es missing index) y cuando ejecutemos algunas de estas vistas dinámicas nos dará dicha información.

Nada mas y nada menos.

Hugo Román Bernachea
Mail de contacto: SQLServer777@gmail.com

Microsoft Certified DBA
Microsoft Certified Trainer
Twitter: @bernachea

Read More...

Encontrar la última sentencia ejecutada para un SPID determinado

Existen ocasiones cuando necesitamos encontrar la última sentencia que fué enviada por un usuario a una instancia SQL Server. Hay un par de maneras de hacer esto.

Abrimos dos sessiones diferentes en Query Analizer (SQL 2000) o Management Studio (2005).

En la primera sesión ejecutamos:

select @@spid

Esto nos devuelve el SPID de proceso.

Ahora ejecutamos:

SELECT table_name, table_Type
FROM INFORMATION_SCHEMA.TABLES
WHERE table_Type = ‘BASE TABLE’
GO

Ahora nos vamos a la otra sessión y ejecutamos:

DBCC INPUTBUFFER (spid)

Obviamente reemplazamos "spid" con el valor obtenido de la consulta anterior.
Y veremos la consulta ejecutada en la otra session.

Otra forma de lograr lo mismo es usar: fn_get_sql. Esta función fué introducida recién en el Service Pack 3 de SQL Server 2000, de modo que si no tienen dicho SP no la van a encontrar disponible.

La primera opción funciona siempre en SQL Server 2000 y 2005.

He aquí como se usa la función fn_get_sql.

DECLARE @HANDLE BINARY(20)
SELECT @HANDLE = sql_handle from sys.sysprocesses where spid = 60
SELECT text FROM ::fn_get_sql(@handle)

En SQL 2005 también tenemos disponibles las DMV (dynamic management view) para obtener dicha información.

DECLARE @handle VARBINARY(64)
SELECT @handle = sql_handle from sys.sysprocesses where spid = 60
SELECT text FROM sys.dm_exec_sql_text(@handle)

Y ya está, demasiado para un día de calor en Buenos Aires.

Hugo Román Bernachea
Mail de contacto: SQLServer777@gmail.com

Microsoft Certified DBA
Microsoft Certified Trainer
Twitter: @bernachea

Read More...

Como transferir logines entre servidores en SQL Server 2000

Cuando se mueven bases de datos a un nuevo servidor, es posible que los
usuarios no puedan iniciar sesión en él. En su lugar, reciben el
siguiente mensaje de error: 
Msj 18456, Nivel 16, Estado 1
Error de inicio de sesión del usuario '%ls'.
Es necesario transferir los inicios de sesión y las contraseñas al nuevo servidor. En este artículo se describe cómo transferir los inicios de sesión y las contraseñas a un nuevo servidor.
Para transferir inicios de sesión y contraseñas entre las diferentes versiones de SQL Server, siga estos pasos:
1. Ejecute la siguiente secuencia de comandos en el origen de SQL Server.
Esta secuencia de comandos crea dos procedimientos almacenados denominados sp_hexadecimal y
sp_help_revlogin en la base de datos master.
Continúe con el paso 2 cuando termine de crear el procedimiento.

Nota: el siguiente procedimiento depende de las tablas de sistema de SQL Server. La estructura de estas tablas puede variar de una versión de SQL Server a otra y no se recomienda realizar la selección directamente desde las tablas de sistema. 
----- Begin Script, Create sp_help_revlogin procedure -----

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
  @binvalue varbinary(256),
  @hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length) BEGIN  DECLARE @tempint int  DECLARE @firstint int  DECLARE @secondint int  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))  SELECT @firstint = FLOOR(@tempint/16)  SELECT @secondint = @tempint - (@firstint*16)  SELECT @charvalue = @charvalue +    SUBSTRING(@hexstring, @firstint+1, 1) +    SUBSTRING(@hexstring, @secondint+1, 1)  SELECT @i = @i + 1 END SELECT @hexvalue = @charvalue GO  IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL  DROP PROCEDURE sp_help_revlogin GO CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS DECLARE @name    sysname DECLARE @xstatus int DECLARE @binpwd  varbinary (256) DECLARE @txtpwd  sysname DECLARE @tmpstr  varchar (256) DECLARE @SID_varbinary varbinary(85) DECLARE @SID_string varchar(256)  IF (@login_name IS NULL)  DECLARE login_curs CURSOR FOR    SELECT sid, name, xstatus, password FROM master..sysxlogins    WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
  SELECT sid, name, xstatus, password FROM master..sysxlogins
  WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
  PRINT ''
  SET @tmpstr = '-- Login: ' + @name
  PRINT @tmpstr
  IF (@xstatus & 4) = 4
  BEGIN -- NT authenticated account/group
    IF (@xstatus & 1) = 1
    BEGIN -- NT login is denied access
      SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
      PRINT @tmpstr
    END
    ELSE BEGIN -- NT login has access
      SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
      PRINT @tmpstr
    END
  END
  ELSE BEGIN -- SQL Server authentication
    IF (@binpwd IS NOT NULL)
    BEGIN -- Non-null password
      EXEC sp_hexadecimal @binpwd, @txtpwd OUT
      IF (@xstatus & 2048) = 2048
        SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
      ELSE
        SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
      PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
      SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
        + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
    END
    ELSE BEGIN
      -- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
      SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
        + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
    END
    IF (@xstatus & 2048) = 2048
      -- login upgraded from 6.5
      SET @tmpstr = @tmpstr + '''skip_encryption_old'''
    ELSE
      SET @tmpstr = @tmpstr + '''skip_encryption'''
    PRINT @tmpstr
  END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
----- End Script -----

2.Una vez creado el procedimiento almacenado sp_help_revlogin,
ejecute este procedimiento desde el Analizador de consultas del servidor de origen.
El procedimiento almacenado sp_help_revloginse puede utilizar tanto en
SQL Server 7.0 como en SQL Server 2000.
El resultado de este procedimiento almacenado son secuencias de comandos de inicio
de sesión que crean inicios de sesión con el SID y las contraseñas originales.
Guarde el resultado y, a continuación, péguelo y ejecútelo en el
Analizador de consultas del servidor SQL Server de destino. Por ejemplo:

EXEC master..sp_help_revlogin
Hugo Román Bernachea
Mail de contacto: SQLServer777@gmail.com

Microsoft Certified DBA
Microsoft Certified Trainer
Twitter: @bernachea

Read More...

Manejando fechas de la manera correcta en SQL Server

Creo que siempre existe un bache en el tema del manejo de fechas en SQL Server, de modo que voy a tratar de clarificar algunos puntos en este artículo.

Existen dos tipos de datos para almacenar fecha en SQL Server, Datetime y SmallDatetime.

La diferencia entre ambas es el espacio que ocupan y la precisión de cada tipo de datos. Datetime es el tipo de datos mas preciso.

El punto es saber como es que SQL Server almacena internamente las fechas. Algunos pensarán en los formatos comunes mm/dd/yyyy o dd/mm/yyyy o formatos similares. Pero no es así.
SQL Server en realidad almacena las fechas como un número entero de 8 bytes, con los primeros 4 bytes almacenados fecha y los otros 4 bytes almacenado la hora. El Smalldatetime por su parte ocupa la mitad de esto, 4 bytes totales, 2 para fecha y 2 para la hora.

Ahora bien, como gente astuta que sois me preguntareis porque motivois (me pintó el hispánico) los selects traen la fecha en un formato dado (americano/español, japonés, coreano, papuense, etc)????
Ese formato me genera un problema al momento de filtrar por fechas, porque 3/1/2008 podría en realidad resultar el 1 de marzo del 2008 y no el 3 de enero del 2008. Me explico hasta aquí??

Como hago entonces para librarme de las configuraciones regionales, seteos, formatos y ser libre de todas estas cosas y despreocuparme por siempre y que mis consultas filtradas por fechas funcionen siempre??

Alguno de ustedes dirá que quizás usando el Standard ANSI y yo le contesto SI ! Esa es la respuesta !. El estandar ansi es así:
YYYYMMDD HH:mm:ss’
Ese es el formato que yo tendría que usar para mis consultas.
Veamos el caso ejemplificado con Northwind la cual viene con SQL Server 2000 o pueden bajarlo directamente desde Microsoft en el peor de los casos.
/* Creo un usuario con idioma predeterminado en Español */ 
sp_addlogin 'usuario','pepenervos','master','Español'
 
/* vamos a darle acceso a Northwind */
use Northwind
GO
sp_grantdbaccess 'usuario' 
GO
Nos logueamos en el Query Analizer como "usuario".
Con la siguiente instruccion confirmamos el idioma español para el usuario "usuario":
Select @@Language
La consulta devuelve:
---------------------------------------------------------
Español
(1 filas afectadas)
Ahora usamos la tabla Orders y hacemos la consulta de fechas de la forma tradicional y la segunda usando el formato ANSI propuesto.
use northwind
go
 
-- la opcion que siempre hemos usado hasta hoy que os he mostrado el camino
select count(*) from orders where orderdate >='01-08-1997'
 
-- La opción que cambiará vuestras vidais.
select count(*) from orders where orderdate >='19970801'
Ambas consultas devuelven 460 registros para pedidos posteriores al 1 de agosto del 97.
Cambiamos el lenguaje al idioma inglés y volvemos a probar a ver que zapa.
use northwind
go
 
SET LANGUAGE us_english
GO
 
-- ya dijimos que esta opción no corre mas.
select count(*) from orders where orderdate >='01-08-1997'
 
-- la opción ANSI que hizo que dejeis de sufrir.
select count(*) from orders where orderdate >='19970801'
 
La primera consulta devolvió 670 registros y la segunda sigue devolviendo la cantidad correcta, 460 registros. Lo que ocurrió es que al cambiar el formato regional la primera consulta en realidad está buscando registros mayores el 8 de enero y eso no tiene nada que ver con el 1 de agosto que nosotros queremos filtrar !

La segunda sentencia sigue siendo correcta, de modo que ya saben, a usar el formato ANSI que se acaba el mundo.
Y si quiero traer solamente los registros de un solo día, teniendo en cuenta que el ansi me toma la hora y eso puede hacer que no devuelva todos los registros de ese dia, podemos escribir algo como lo que sigue:

Select * from orders Where orderdate >='19970805' and orderdate <>

En pocas palabras, le sumo un dia a la fecha que estoy buscando y hago que el filtro funcione por menor a ese dia, de ese modo vienen todos los registros del dia que quiero recuperar.

Y listo el pollo. Una solución sencilla y efectiva al tema de las fechas.


Hugo Román Bernachea
Mail de contacto: SQLServer777@gmail.com

Microsoft Certified DBA
Microsoft Certified Trainer
Twitter: @bernachea

Read More...

Creando cursores en Transact-SQL

Un cursor es una variable que nos permite recorrer con un conjunto de resultados obtenido a través de una sentencia SELECT fila a fila. Los siguientes pasos nos permiten definir y utilizar cursores:
  • Declarar el cursor, utilizando DECLARE
  • Abrir el cursor, utilizando OPEN
  • Leer los datos del cursor, utilizando FETCH ... INTO
  • Cerrar el cursor, utilizando CLOSE
  • Liberar el cursor, utilizando DEALLOCATE

La sintaxis general para trabajar con un cursor es la siguiente.

-- Declaración del cursor
DECLARE nombre_cursor CURSOR FOR sentencia_sql;

-- apertura del cursor 
OPEN nombre_cursor; 

-- Lectura de la primera fila del cursor 
FETCH nombre_cursor; INTO lista_variables;
WHILE (@@FETCH_STATUS = 0)
BEGIN 
-- Lectura de la siguiente fila de un cursor
FETCH nombre_cursor; INTO lista_variables;
...
END -- Fin del bucle WHILE -- Cierra el cursor CLOSE nombre_cursor; -- Libera los recursos del cursorDEALLOCATE <nombre_cursor>
El siguente ejemplo muestra el uso de un cursor.

-- Declaracion de variables para el cursor
DECLARE @Id int,

@Nombre varchar(255),

@Apellido1 varchar(255),

@Apellido2 varchar(255),

@NifCif varchar(20),

@FxNacimiento datetime


-- Declaración del cursor

DECLARE cClientes CURSOR FOR

SELECT  Id, Nombre, Apellido1,
Apellido2, NifCif, FxNacimiento

FROM CLIENTES

-- Apertura del cursorOPEN cClientes

-- Lectura de la primera fila del cursor

FETCH cClientes INTO    @id, @Nombre, @Apellido1, @Apellido2, @NifCif, @FxNacimiento
WHILE (@@FETCH_STATUS = 0 )
BEGIN
PRINT @Nombre + ' ' + @Apellido1 + ' ' + @Apellido2
-- Lectura de la siguiente fila del cursor
FETCH cClientes INTO @id, @Nombre, @Apellido1, @Apellido2, @NifCif, @FxNacimiento
END -- Cierre del cursor
CLOSE cClientes

-- Liberar los recursos
DEALLOCATE cClientes

 
Cuando trabajamos con cursores, la funcion @@FETCH_STATUS nos indica el estado de la última instrucción FETCH emitida, los valores posibles son:
Valor devuelto Descripción
0 La instrucción FETCH se ejecutó correctamente.
-1 La instrucción FETCH no se ejecutó correctamente o la fila estaba más allá del conjunto de resultados.
-2 Falta la fila recuperada.
En la apertura del cursor, podemos especificar los siguientes parámetros:
DECLARE <nombre_cursor> CURSOR
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR >
El primer conjunto de parámetros que podemos especificar es [ LOCAL | GLOBAL ]. A continuación mostramos el significado de cada una de estas opciones.

  • LOCAL
    Especifica que el ámbito del cursor es local para el proceso por lotes, procedimiento almacenado o desencadenador en que se creó el cursor.

DECLARE cClientes CURSOR LOCAL FOR

SELECT  Id, Nombre, Apellido1, 

Apellido2, NifCif, FxNacimiento

FROM CLIENTES 

  • GLOBAL
    Especifica que el ámbito del cursor es global para la conexión. Puede hacerse referencia al nombre del cursor en cualquier procedimiento almacenado o proceso por lotes que se ejecute en la conexión.

DECLARE cClientes CURSOR GLOBAL FOR

SELECT  Id, Nombre, Apellido1, 

Apellido2, NifCif, FxNacimiento

FROM CLIENTES 
Si no se especifica GLOBAL ni LOCAL, el valor predeterminado se controla mediante la configuración de la opción de base de datos default to local cursor.
El siguiente conjunto de parámetros que podemos especificar es [ FORWARD_ONLY | SCROLL ]. A continuación mostramos el significado de cada una de estas opciones.
  • FORWARD_ONLY
    Especifica que el cursor sólo se puede desplazar de la primera a la última fila. FETCH NEXT es la única opción de recuperación admitida.
 

DECLARE cClientes CURSOR FORWARD_ONLY FOR

SELECT  Id, Nombre, Apellido1, 

Apellido2, NifCif, FxNacimiento

FROM CLIENTES

 
  • SCROLL
    Especifica que están disponibles todas las opciones de recuperación (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE). Si no se especifica SCROLL en una instrucción DECLARE CURSOR la única opción de recuperación que se admite es NEXT. No es posible especificar SCROLL si se incluye también FAST_FORWARD.
    Si se incluye la opción SCROLL, la forma en la realizamos la lectura del cursor varia, debiendo utilizar la siguiente sintaxis: FETCH [ NEXT | PRIOR | FIRST | LAST | RELATIVE | ABSOLUTE ] FROM <>
-- Declaracion de variables para el cursorDECLARE @Id int,

@Nombre varchar(255),

@Apellido1 varchar(255),

@Apellido2 varchar(255),

@NifCif varchar(20),

@FxNacimiento datetime

 

-- Declaración del cursor

DECLARE cClientes CURSOR SCROLL FOR

SELECT  Id, Nombre, Apellido1, 

Apellido2, NifCif, FxNacimiento

FROM CLIENTES

-- Apertura del cursorOPEN cClientes

-- Lectura de la primera fila del cursorFETCH NEXT FROM cClientes 

INTO @id, @Nombre, @Apellido1, @Apellido2, @NifCif, @FxNacimiento

WHILE (@@FETCH_STATUS = 0 )

BEGIN

PRINT @Nombre + ' ' + @Apellido1 + ' ' + @Apellido2
-- Lectura de la siguiente fila del cursor
FETCH NEXT FROM cClientes
INTO @id,@Nombre,@Apellido1,@Apellido2,@NifCif,@FxNacimiento
END -- Lectura de la fila anterior FETCH PRIOR FROM cClientes INTO @id, @Nombre, @Apellido1, @Apellido2, @NifCif, @FxNacimiento PRINT @Nombre + ' ' + @Apellido1 + ' ' + @Apellido2 -- Cierre del cursorCLOSE cClientes -- Liberar los recursosDEALLOCATE cClientes
El siguiente conjunto de parámetros que podemos especificar es [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]. A continuación mostramos el significado de cada una de estas opciones.
  • STATIC
    Define un cursor que hace una copia temporal de los datos que va a utilizar. Todas las solicitudes que se realizan al cursor se responden desde esta tabla temporal de tempdb; por tanto, las modificaciones realizadas en las tablas base no se reflejan en los datos devueltos por las operaciones de recuperación realizadas en el cursor y además este cursor no admite modificaciones.
 

DECLARE cClientes CURSOR STATIC FOR

SELECT  Id, Nombre, Apellido1, 

Apellido2, NifCif, FxNacimiento

FROM CLIENTES 
  • KEYSET
    Especifica que la pertenencia y el orden de las filas del cursor se fijan cuando se abre el cursor. El conjunto de claves que identifica las filas de forma única está integrado en la tabla denominada keyset de tempdb.
 

DECLARE cClientes CURSOR KEYSET FOR

SELECT  Id, Nombre, Apellido1, 

Apellido2, NifCif, FxNacimiento

FROM CLIENTES 
  • DYNAMIC
    Define un cursor que, al desplazarse por él, refleja en su conjunto de resultados todos los cambios realizados en los datos de las filas. Los valores de los datos, el orden y la pertenencia de las filas pueden cambiar en cada operación de recuperación. La opción de recuperación ABSOLUTE no se puede utilizar en los cursores dinámicos.
 

DECLARE cClientes CURSOR DYNAMIC FOR

SELECT  Id, Nombre, Apellido1, 

Apellido2, NifCif, FxNacimiento

FROM CLIENTES
  • FAST_FORWARD
    Especifica un cursor FORWARD_ONLY, READ_ONLY con las optimizaciones de rendimiento habilitadas. No se puede especificar FAST_FORWARD si se especifica también SCROLL o FOR_UPDATE.
 

DECLARE cClientes CURSOR FAST_FORWARD FOR

SELECT  Id, Nombre, Apellido1, 

Apellido2, NifCif, FxNacimiento

FROM CLIENTES 
En SQL Server 2000, las opciones de cursor FAST_FORWARD y FORWARD_ONLY se excluyen mutuamente. Si se especifican ambas, se genera un error. En SQL Server 2005, las dos palabras clave se pueden utilizar en la misma instrucción DECLARE CURSOR.
El siguiente conjunto de parámetros que podemos especificar es [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]. A continuación mostramos el significado de cada una de estas opciones.
  • READ_ONLY
    Evita que se efectúen actualizaciones a través de este cursor. No es posible hacer referencia al cursor en una cláusula WHERE CURRENT OF de una instrucción UPDATE o DELETE. Esta opción reemplaza la capacidad de actualizar el cursor.
 

DECLARE cClientes CURSOR READ_ONLY FOR

SELECT  Id, Nombre, Apellido1, 

Apellido2, NifCif, FxNacimiento

FROM CLIENTES
  • SCROLL_LOCKSEspecifica que se garantiza que las actualizaciones o eliminaciones posicionadas realizadas a través del cursor serán correctas. Microsoft SQL Server bloquea las filas cuando se leen en el cursor para garantizar que estarán disponibles para futuras modificaciones. No es posible especificar SCROLL_LOCKS si se especifica también FAST_FORWARD o STATIC.
 

DECLARE cClientes CURSOR SCROLL_LOCKS FOR

SELECT  Id, Nombre, Apellido1, 

Apellido2, NifCif, FxNacimiento

FROM CLIENTES
  • OPTIMISTIC
    Especifica que las actualizaciones o eliminaciones posicionadas realizadas a través del cursor no se realizarán correctamente si la fila se ha actualizado después de ser leída en el cursor. SQL Server no bloquea las filas al leerlas en el cursor. En su lugar, utiliza comparaciones de valores de columna timestamp o un valor de suma de comprobación si la tabla no tiene columnas timestamp, para determinar si la fila se ha modificado después de leerla en el cursor. Si la fila se ha modificado, el intento de actualización o eliminación posicionada genera un error. No es posible especificar OPTIMISTIC si se especifica también FAST_FORWARD.
 

DECLARE cClientes CURSOR OPTIMISTIC FOR

SELECT  Id, Nombre, Apellido1, 

Apellido2, NifCif, FxNacimiento

FROM CLIENTES 
Por último, queda la opción TYPE_WARNING
  • TYPE_WARNINGEspecifica que se envía un mensaje de advertencia al cliente si el cursor se convierte implícitamente del tipo solicitado a otro.
 

DECLARE cClientes CURSOR TYPE_WARNING FOR

SELECT  Id, Nombre, Apellido1, 

Apellido2, NifCif, FxNacimiento

FROM CLIENTES 
Podemos especificar multiples parámetros en la apertura de cursor, pero unicamente un parámetro de cada grupo. Por ejemplo:
 

DECLARE cClientes CURSOR LOCAL STATIC TYPE_WARNING FOR

SELECT  Id, Nombre, Apellido1, 

Apellido2, NifCif, FxNacimiento

FROM CLIENTES 
Para actualizar los datos de un cursor debemos especificar FOR UPDATE despues de la sentencia SELECT en la declaración del cursor, y WHERE CURRENT OF en la sentencia UPDATE tal y como muestra el siguiente ejemplo.
-- Declaracion de variables para el cursorDECLARE @Id int,

@Nombre varchar(255),

@Apellido1 varchar(255),

@Apellido2 varchar(255),

@NifCif varchar(20),

@FxNacimiento datetime

-- Declaración del cursorDECLARE cClientes CURSOR FOR

SELECT  Id, Nombre, Apellido1, 

Apellido2, NifCif, FxNacimiento

FROM CLIENTES

FOR UPDATE

 

-- Apertura del cursorOPEN cClientes

-- Lectura de la primera fila del cursorFETCH cClientes
INTO @id, @Nombre, @Apellido1, @Apellido2, @NifCif, @FxNacimiento


WHILE (@@FETCH_STATUS = 0 )

BEGIN

UPDATE Clientes
SET APELLIDO2 = isnull(@Apellido2,'') + ' - Modificado'
WHERE CURRENT OF cClientes
-- Lectura de la siguiente fila del cursor
FETCH cClientes INTO @id, @Nombre, @Apellido1, @Apellido2, @NifCif, @FxNacimiento
END -- Cierre del cursor

CLOSE cClientes

-- Liberar los recursos

DEALLOCATE cClientes
Hugo Román Bernachea
Mail de contacto: SQLServer777@gmail.com

Microsoft Certified DBA
Microsoft Certified Trainer
Twitter: @bernachea

Read More...

Resetear un campo identity en SQL Server

Los campos identity equivalen a los campos autonuméricos de otros tipos de bases de datos y aumentan secuencialmente, si eliminamos algunos registros de una tabla los valores anteriormente usados no vuelven a quedar disponibles y no hay una forma obvia de resetear el valor identity.

La forma de modificar el identity es:
DBCC CHECKIDENT(tabla, reseed, 0)

En lugar de tabla ponga el nombre de su tabla.

Estos consejos son dados "as is" y quedan a exclusiva responsabilidad de los usuarios el buen o mal uso de los mismos.





Hugo Román Bernachea
Mail de contacto: SQLServer777@gmail.com

Microsoft Certified DBA
Microsoft Certified Trainer
Twitter: @bernachea

Read More...

Mas de 500 ejemplos de modelos de bases de datos !!! - Espectacular !!!

Un verdadero hallazgo:
http://www.databaseanswers.org/data_models/index.htm

En esta página Barry Williams publicó una lista de mas de 500 ejemplos modélicos de esquemas de bases de datos. Este es un impresionante trabajo que el amigo Barry expone gratuitamente para todos nosotros.

Hugo Román Bernachea
Mail de contacto: SQLServer777@gmail.com

Microsoft Certified DBA
Microsoft Certified Trainer
Twitter: @bernachea

Read More...

Como leer un archivo xml para luego procesarlo con OpenXML ??

Muchas veces queremos procesar un archivo XML directamente en SQL, el tema es cual es la operatoria, usando transact-sql, para introducir el contenido del archivo en una variable Xml para luego procesar dicha variable con openxml.

El siguiente script puede ser una interesante opción para lograr el objetivo, utilizando una tabla temporaria y llenándola con la ejecución del comando "type" de líneas de comando (xp_cmdshell) y luego recorriendo el contenido de dicha tabla llenando la variable tan deseada por nosotros.

He aquí el script:

DECLARE @FileName varchar(255)
DECLARE @ExecCmd VARCHAR(255)
DECLARE @y INT
DECLARE @x INT
DECLARE @FileContents VARCHAR(8000)
declare @idoc int
DROP TABLE #tempXML
CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))

SET @FileName = 'E:\Export\CheckoutReport.xml'
SET @ExecCmd = 'type ' + @FileName
SET @FileContents = ''

INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd
SELECT @y = count(*) from #tempXML
-- SELECT @y = 95
SET @x = 0
WHILE @x <> @y
BEGIN
SET @x = @x + 1
SELECT @FileContents = @FileContents + ThisLine from #tempXML WHERE PK = @x
END
PRINT @FileContents
SELECT @FileContents as FileContents

exec sp_xml_preparedocument @idoc OUTPUT, @FileContents
-- SELECT stmt using OPENXML rowset provider
SELECT *
--FROM OPENXML ('E:\Export\CheckoutReport.xml', '/CheckoutReport/PurchaseList/BuyerPurchase',2)
FROM OPENXML (@idoc, '/CheckoutReport/PurchaseList/BuyerPurchase',2)
WITH (
InvoiceNumber VARCHAR(50) 'TransactionInfo/InvoiceNumber',
ShipmentNumber VARCHAR(50) 'ShipmentList/Shipment/ShipmentNumber',
ShipNameTitle VARCHAR(50) 'ShipmentList/Shipment/ShipNameTitle',
LotTitle VARCHAR(50) 'ShipmentList/Shipment/Items/Item/LotTitle',
SKU VARCHAR(20) 'ShipmentList/Shipment/Items/Item/SKU',
SiteName VARCHAR(50) 'ShipmentList/Shipment/Items/Item/SiteName'
)
EXEC sp_xml_removedocument @idoc

--SELECT * FROM #tempXML

-- DROP TABLE #tempXML

Actualización 24-10-2009
Adicionalmente, si se desea ingresar datos a la tabla directamente desde un archivo, el siguiente script puede ser la solución:

INSERT INTO base..tabla
SELECT xCol
FROM (SELECT * FROM OPENROWSET (BULK 'C:\directorio\archivoxml.xml', SINGLE_BLOB) AS xCol) AS R(xCol)

http://msdn.microsoft.com/en-us/library/ms345117%28SQL.90%29.aspx



Hugo Román Bernachea
Mail de contacto: SQLServer777@gmail.com

Microsoft Certified DBA
Microsoft Certified Trainer
Twitter: @bernachea

Read More...

Blog de Scalability Experts

http://blog.scalabilityexperts.com/

Este blog contiene artículos avanzados sobre SQL Server 2000/2005/2008 acerca de migraciones, escalabilidad, seguridad, etc, etc.

Muy bueno, absolutamente recomendable !

Hugo Román Bernachea
Mail de contacto: SQLServer777@gmail.com

Microsoft Certified DBA
Microsoft Certified Trainer
Twitter: @bernachea

Read More...