Procedimientos no documentados sp_MSforeachtable y sp_MSforeachdb. Parte I

Microsoft SQL Server ofrece dos procedimientos almacenados sin documentar que le permiten procesar todas las tablas de una base de datos o bases de datos todo en una instancia de SQL Server. El primer procedimiento almacenado (SP), "sp_MSforeachtable", le permite procesar fácilmente un código contra todas las tablas en una sola base de datos. El SP otro ", sp_MSforeachdb", se ejecutará una instrucción T-SQL contra cualquier base de datos asociada a la instancia actual de SQL Server.

El "sp_MSforeachtable" SP viene con SQL Server, pero no está documentado en Books online. Este SP se pueden encontrar en la BD Master y se utiliza para procesar un solo comando T-SQL o un número de diferentes comandos T-SQL en contra de todas las tablas de una base de datos dada. Para demostrar cómo funciona vamos con un ejemplo.

Digamos que quiero crear una tabla temporal que contendrá una serie de registros, uno para cada tabla en la base de datos y que en cada fila contenga el nombre de la tabla y el número de filas de la tabla dada. Para hacer esto se desea ejecutar un comando como "select '' count (*) from '' where ''" donde por cada tabla en su base de datos se debe insertar los resultados en una tabla temporal. Entonces, vamos a ver cómo podemos hacer esto usando un cursor y luego usar el SP "sp_MSforeachtable":

Con cursor:

use pubs
go
set nocount on
declare @cnt int
declare @table varchar(128)
declare @cmd varchar(500)
create table #rowcount (tablename varchar(128), rowcnt int)
declare tables cursor for
select table_name from information_schema.tables
   where table_type = 'base table'
open tables
fetch next from tables into @table
while @@fetch_status = 0
begin
  set @cmd = 'select ''' + @table + ''', count(*) from ' + @table
  insert into #rowcount exec (@cmd)
  fetch next from tables into @table
end
CLOSE tables
DEALLOCATE tables
select top 5 * from #rowcount
    order by tablename
drop table #rowcount

Con "sp_MSforeachtable":

use pubs
go
create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable
   'insert into #rowcount select ''?'', count(*) from ?'
select top 5 * from #rowcount
    order by tablename
drop table #rowcount

Ambos tienen salidas similares, pero el segundo es más corto y de mejor rendimiento. Esta es la descripción de sus argumentos:

exec @RETURN_VALUE=sp_MSforeachtable @command1, @replacechar, @command2,
  @command3, @whereand, @precommand, @postcommand

donde

• @RETURN_VALUE - es el valor de retorno 
• @command1 - es el primer comando en ser ejecutado por "sp_MSforeachtable" y definido como un nvarchar(2000)
• @replacechar - es un caracter que podrá ser reemplazado con el nombre de la tabla que esta siendo procesada (valor por omisión es "?")
• @command2 y @command3 son comando adicionales que puede sercorridos para cada tabla, donde @command2 correo después de @command1, y @command3 correrá luego de @command2
• @whereand - este argumento puede ser usadopara agregar  constraints adicionales para ayudar a identificar las filas en la tabla sysobjects que será seleccionada, este argumento es un nvarchar(2000)
• @precommand - es un argumento nvarchar(2000) que especifica un comado para ser ejecutado antes que se procese cualquier tabla.
• @postcommand - también es un nvarchar(2000) que sirve para identificar un comando que será ejecutado luego de todos los comandos que han sido ejecutados.

Veamos un ejemplo con @whereand donde el nombre de una tabla empiece con "p":

use pubs
go
create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable
@command1 = 'insert into #rowcount select ''?'',
              count(*) from ?',
@whereand = 'and name like ''p%'''
select top 5 * from #rowcount
    order by tablename
drop table #rowcount

Bueno luego seguimoes este tema, acá los dejo por hoy.

Procedimiento almacenado sp_executesql

Para ejecutar una cadena, se recomienda utilizar el procedimiento almacenado sp_executesql, en lugar de una instrucción EXECUTE: ver aquí

Sharepoint 2010: desde cero!! Parte II

Hola gente!!

Pues bien, acá estamos de nuevo. Esta vez les mostraré como instalar MSS 2010 sobre la infraestructura virtual que explicamos en el post anterior.
Lo primero y más escencial es: saber que arquitectura vamos a usar; y lo segundo: para qué la vamos a usar. Parece tonto, pero créanme no lo es, ya que les evitará retrabajo y devolver lo andado. Yo lo hice así (gracias a un buen profesor que tuve de MOSS 2007), pasos más pasos menos:

1. Identificar que tengo de hardware (que para este caso lo virtualicé con VMWare) para saber sus capacidades.
2. Calcular (basado en número de procesadores, RAM disponible, etc.) cuantas máquinas virtuales puedo construir. Esto es básico!!
3. Con base a lo anterior, planear el tipo de granja que mi hardware podría soportar, es decir, si voy a crear una granja solamente para colaboración, solamente para servicio de reportes o bien para inteligencia empresarial, para poner unos ejemplos. La granja que administro es para inteligencia empresarial, por cuanto, necesito que posea alta disponibilidad, balanceo de cargas, redundancia en energía, redundancia de red, descentralización de almacenamiento, etc.
4. Aterrizando ya con Sharepoint, necesito saber si voy a instalar un small farm, medium farm o lo que se conoce como topología con server groups. Cada una lleva sus propias consideraciones, las cuales están muy bien explicadas en estos diagramas técnicos que tuve que leer.
5. Escogí un small farm de cuatro servidores (escogencia basada en todo lo anterior), que luego escalaré a mediano plazo a un medium farm. Mi distribución es: 2 WFE's en NLB, 1 App Server y 1 DB Server.
6. También es importante medir de alguna forma la cantidad de usuarios que podría tener tu sitio. En mi caso, el worst case scenario tendría a unos 300 usuarios al mismo tiempo, por lo que el tema del NLB es preponderante para mi. Además, de que tengo una plataforma de reportes (SRSS 2008 R2) que debo administrar, aproveché el NLB hecho para escalar el servicio de reportes también.

Bueno hasta aquí llega el resumen de consideraciones. Vamos al grano. Si usan Windows Server 2008 R2 como yo, pues deberán instalarlo por medio de la sección de consola del vSphere Client. Esto en realidad hay que hacerlo con cualquier SO la primera vez:

Luego instalen el SP1 para Windows Server 2008 R2 (para que no tengan problemas bájenlo por aparte, no dejen que el mismo Windows lo haga, dura demasiado!!). Por razones de confidencialidad, no puedo decirles cuales son las características de hardware de las vMachines (cantidad de RAM, número de procesadores), pero lo que deben tomar en cuenta es lo siguiente:

• De las 4 vMachines, la más robusta es la que lleva la carga de SQL Server. Es la que tiene más RAM y procesadores, y obviamente, más disco duro.
• La que lleva la carga del Central Administration es un poco menos robusta que la anterior, puesto que esta se usará más para dar servicios.
• Y las más livianas (y de igual configuración...) son las de WFE. Eso si, les recomiendo al menos 4 procesadores en cada una. Estas llevarán la carga de los proxys de SRSS, el BLOB chaching y algún otro servicio para cliente final. Les recomiendo que instalen y configuren una, para que la clonen con el VCenter, es más rápido que instalarla de cero.

Y así, mágicamente, luego de muchas reiniciadas... empezamos con Sharepoint. Como ha costado!!

Vuelvo a repetir, no es una receta escrita en piedra, habrá cosas que se pueden hacer a posteriori. Lo primero que hice (luego de leer un par de foros) fue: instalar en forma básica el IIS 7.5 (viene con Windows 2008 R2) en cada servidor de la granja (todo se basa en Web Applications). A diferencia de MOSS 2007, éste se ocupa en cuanto servidor metan a la solución.

Hecho lo anterior, empecemos a instalar SQL Sever 2008 R2 (la versión Denali funciona con Sharepoint luego de instalar el SP1 de éste) en el servidor que está destinado para eso:

Vamos a la sección Installation y escogemos New installation...

Me brinco las imágenes de la instalación (irrelevante mostraralas...) de los support files y vamos a la parte de Feature Installation...

 Luego, escogemos las opciones que se necesiten instalar. En mi caso escogí el Engine Services, Analysis y Reporting Services, por la naturaleza de mi granja.

Deben recordar varias cosas, si van a instalar Reporting Services, dejen la opción "Install, but do not configure the report server" seleccionada para que posteriormente se pueda configurar a la integración desde la instalación de Sharepoint.

Recuerden activar la opción de FILESTREAM, la cual permite soportar el BLOB file storage (igual se puede configurar luego).

Recuerden que las cuentas de servicio deberían ser (en la medida de lo posible) cuentas de dominio.

La cuenta con la que deberías instalar (ojalá de dominio también) SQL Server debe ser parte de los administradores de Windows de ese servidor, además, deberías agregarla a la lista de cuentas del Engine Configuration:

Por lo demás, termina de darle "Next" e instala. Luego de eso, reinicia el servidor. Logueate con la cuenta que instalaste el SQL Server e instala el SP1 de SQL Server 2008 R2. Deberás bajarlo de aquí. Con esto te aseguras de tener la última versión del engine. Verifica en la sección de Updates de Windows si necesitas más actualizaciones, si es así instalalas, reinicias y listo.

Ya tenemos nuestro primer servidor configurado en su primera etapa (todavía falta mucho camino...), el cual nos permitirá albergar las diferentes bases de datos que Sharepoint crea. En nuestro siguiente post, veremos la forma de instalar MSS 2010 usando este servidor de base de datos.

Nos vemos.

Uso de CLR stored procedure para llamar a un web service desde SQL Server 2008

Hola gente! Pues acá les traigo una forma de acceder a un servicio web desde un procedimiento almacenado usando el CLR.

Let's begin!

Para hacer práctico el ejemplo, lo haremos con el servicio web del Banco Central de Costa Rica que nos permite consumir los diferentes tipos de cambio monetario. Para este caso escogeremos el tipo de cambio del dólar estadounidense (venta del USD), cuyo valor de parámetro es 318.

Para que vayan comprendiendo les coloco la arquitectura que vamos a utilizar:

La idea es consumir el valor dela venta en USD y la fecha de dicho valor e ingresarlos a una tabla, la cual, puede ser consultada posteriormente para realizar ajustes, cálculos, etcétera en sistemas que dependan de las fluctuaciones de ésta moneda.

Vamos al grano, lo primero es ir a nuestro servidor (físico) que tiene SQL Server 2008 y habilitarle el CLR:

sp_configure 'clr enabled', 1
go
reconfigure
go

Como segundo paso es permitirle a la base de datos que vas a utilizar como repositorio, acceder a recursos externos del motor de SQL:

alter database TU_BASE_DATOS set trustworthy on

El tercer paso (asumiendo que tienen .NET o al menos Framework 2.0) es crear un proxy local del servicio web del banco para convertirlo luego en una dll que será empotrada al SQL. Para lo cual necesitamos de la utilería WSDL.exe que se ubica en Visual Studio SDK/Bin:

wsdl /o:CambioDolar.cs /n:cambioDolar.Test http://indicadoreseconomicos.bccr.fi.cr/IndicadoresEconomicos/WebServices/wsIndicadoresEconomicos.asmx

Lo anterior creará una clase en C# (CambioDolar.cs) que contendrá el código proxy y el namespace de la clase creada (cambioDolar.Test).

El cuarto paso (en el IDE de .NET), es crear el cuerpo del CLR stored procedure:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using cambioDolar.Test;

namespace ConsumidorCambioDolar
{
public class consumidorCambioDolar
{
  private static void insertarIndicador(SqlDateTime Fecha, SqlDouble Valor)
  {
   ...
  }

  [SqlProcedure]
  public static void obtenerIndicador(SqlString Indicador)
  {
   ...
     insertarIndicador(DateTime.Parse(x.ToString()), double.Parse(y.ToString()));
  }
}
}

Observese la invocación del namespace que permite instanciar la referencia de la clase proxy  y la etiqueta [SqlProcedure] sobre la firma del método obtenerIndicador que le permite a SQL Server invocar al método a través de la firma del procedimiento almacenado que construiremos más adelante.

En el quinto paso vamos a utilizar otra utilería (CSC.exe) para compilar el CLR Stored Procedure y la clase proxy en una sola dll, además, crearemos el XML Serialization Code debido a que la serialización XML dinámica no está permitida en SQL Server, por lo que debemos ejecutar la utiliría SGEN.exe para generar un ensamblado de serialización estática.

// Crea ConsumidorCambioDolar.dll

csc /t:library ConsumidorCambioDolar.cs CambioDolar.cs

// Genera el XML Serialization Code
// Crea ConsumidorCambioDolar.XmlSerializers.dll
sgen /a:ConsumidorCambioDolar.dll

Por último, sería empotrar los esamblados y crear el store procedure en SQL Server:

CREATE ASSEMBLY consumidorCambioDolar
FROM 'E:\ConsumidorCambioDolar.dll'
WITH PERMISSION_SET = UNSAFE;
GO

--DROP ASSEMBLY consumidorCambioDolar

CREATE ASSEMBLY [consumidorCambioDolar.XmlSerializers]
FROM 'E:\ConsumidorCambioDolar.XmlSerializers.dll'
WITH PERMISSION_SET = SAFE;
GO

--DROP ASSEMBLY [consumidorCambioDolar.XmlSerializers]
CREATE PROCEDURE prc_InsertarIndicadorEconomico(Indicador nvarchar(5))
AS
EXTERNAL NAME consumidorCambioDolar.consumidorCambioDolar.obtenerIndicador
GO

Bueno llegamos al final, recuerda que los dos dll's debes colocarlos en un lugar seguro, debes colocar el store procedure en la invocación de un job para que sea desatendido y si quieres quitar todo aplica los drop's de abajo para arriba. Nos vemos...

Eliminar registros duplicados en una tabla con SQL Server 2008

Hola gente, acá les tengo un código en una sola línea para eliminar esos molestos registros duplicados en tablas de bases de datos relacionales.

Entonces, cuando se nos presenta el caso de que muchas veces existan registros de datos de empleados, por ejemplo, que al ser exportados a una base de datos quedan con ciertas diferencias lexicográficas (p.e.: López, Lopez, loPez, etc.) que son difíciles de detectar, necesitamos un código que pueda ser utilizado masivamente, pero que sea flexible. Por lo que, éste algoritmo te permite permutar las búsquedas con el campo o campos de una tabla que necesites para realizar los filtros. Sin más preámbulo, aquí está el código:

delete tabla from (select fila = row_number() over(partition by tu_campo1, [tu_campo2] order by algun_campo) from tu_tabla) as tabla where fila <> 1

Cambiar el idioma por omisión de tu motor SQL Server

Mira puedes ejecutar los siguientes comandos:

SELECT @@LANGUAGE AS 'Language Name' -- Este comando es para saber en que lenguaje se encuentra.


SET LANGUAGE Spanish

exec sp_defaultlanguage sa, 'spanish'

Luego de eso, reinicia el servidor y listo. Nos vemos.

Reiniciar campo tipo identity para SQL Server

Hola amigos, aquí les dejo una instrucción para esa gran pregunta: "Puedo porner en cero de nuevo un campo identity una vez usado?", respuesta: sí.

Lo hacemos con un comando DBCC:

DBCC CHECKIDENT (nombre_tabla, RESEED,0)
Solamente coloquen el nombre de la tabla y listo. Nos vemos.

Comprimir TempDB en SQL Server

Debido a situaciones de origen laboral, en la oficina se ha tenido que contar muchas veces con la opción de comprimir la base de datos temporales de sistema que tiene SQL Server (2005-2008).

Encontre dos maneras (lo que indica que no existan más formas) de realizar esa tarea, los describo de seguido.

Método 01: Comprimir y modificar archivo

Según leí, Microsoft desestima comprimir cualquier TempDB que este siendo usada constantemente. Esto es porque "you may receive multiple consistency errors" que podrían no solamente interrumpir actividades de usuarios cuando se da la operación de compresión. Sin embarga esa actividad normalmente es mínima o nula, en todo caso, aquí están los pasos:

1. Corra el comando DBCC SHRINKFILE en cada archivo que usted quiera reducir el tamaño:
   USE TempDB
   GO
   DBCC SHRINKFILE (N'logical_file_name', 5) -- reducir en 5 MB
2. Luego corra ALTER DATABASE, con el tamaño que usted quiere que sean. Esto causará que el nuevo tamaño se registre en master.sys.master_files, la cual es un catalogo de sistema que SQL Server usa para recrear una nueva TempDB en blanco cada vez que una server/instance es reiniciada.
   USE MASTER
   GO
   ALTER DATABASE TEMPDB MODIFY FILE (NAME=' logical_file_name, SIZE=6MB)

Note que hasta que SQL Server es reiniciado (cuando TempDB es recreada) los cambios no mostraran los nuevos valores en la Database Properties o en la Shrink File GUI. Sin embargo, se pueden verificar los cambios inmediatamente con:

SELECT DB_NAME(DATABASE_ID)DBNAME, [NAME] LOGICAL_FILENAME, [SIZE]*8/1024 SIZE_MB
FROM MASTER.SYS.MASTER_FILESWHERE DB_NAME(DATABASE_ID) = 'TEMPDB'

Método 02: Modificar archivos en modo de usuario restringido

Este método elimina el riesgo de errores de consistencia. Con la salvedad, que tendrás que desconectar a todos los usuarios antes de hacer algún cambio. Sin embargo, si lo planeas bien, se podrá hacer esos cambios en 10 minutos haciendo estos pasos:

1. Establezca una Coenxion Dedicada de Administrador (DAC) en el Management Studio para conectarse. Tan sencillo como anteponiendo "Admin:" en frente de la instacia de nombre. P.E., ADMIN:Rep-Server\Instancia1.

2. Corra:

• ALTER DATABASE with the REMOVE -- opción para marcar los .ndf como obsoletos.
• USE MASTER GO ALTER DATABASE TEMPDB REMOVE FILE logical_file_name

3. Detenga la instancia desde el command prompt. Para la default instance use:

• C:\>NET STOP MSSQLSERVER
• o
• NET STOP "SQL Server (MSSQLSERVER)"
• o para la named instance:
• C:\> NET STOP "SQL Server ( instancename )"
• o
• NET STOP MSSQL$instancename

4. Inicie la instancia en modo restringido con:

• C:\SQL\MSSQL.1\MSSQL\Binn>sqlservr.exe -c -f

5. Mofique la TempDB con el nuevo tamaño inicial para el .mdf y el .ldf desde la conexión dedicada en SSMS (hasta que reinicie SQL Server en modo normal, será la única conexión disponible).

• USE MASTER
• GO
• ALTER DATABASE TEMPDB MODIFY FILE (NAME='logical_file_name', SIZE=6MB)

6. Regrese al command prompt y teclee Ctrl + C para salir del modo restringido (diga si cuando el prompt pregunte si quiere detener SQL Server). Entonces inicie la instacia en modo normal. Para la default instance:

• C:\NET START MSSQLSERVER
• o
• NET STOP "SQL Server (MSSQLSERVER)"

7. Modifique la TempDB con la opción Add File activada con el nuevo tamaño para los archivos .ndf.

• ALTER DATABASE TEMPDB ADD FILE (NAME=logical_file_name, FILENAME='C:\bla\bla\Data\logical_file_name.ndf', SIZE=6MB)

8. Al final corra esta consulta para asegurarse de que todos los cambios fueron realizados apropiadamente en la base de datos Master:

• SELECT DB_NAME(DATABASE_ID)DBNAME, [NAME] LOGICAL_FILENAME, [SIZE]*8/1024 SIZE_MB FROM MASTER.SYS.MASTER_FILES WHERE DB_NAME(DATABASE_ID) = 'TEMPDB'

Podemos observar que el primer método es más simple y rápido, pero pero se corre un riesgo inherente de corromper los datos en la TempDB. Además, la operación de compresión puede no terminar debido al uso concurrente de dichos archivos. Ahora bien, el segundo método es más elaborado pero asegura una operación limpia. Sin embargio requiere detener el sistema.
Espero les sirva.