jueves, 22 de agosto de 2013

TIP SQL: Verificar La Existencia De Un User-defined Data Type/User-defined Table TYPE

Instrucción sencilla que verifica la existencia de un User-defined Data Type/User-defined Table TYPE. La comparto porque esta no está disponible en los 'Templates' de Microsoft SQL Server Management Studio.
IF TYPE_ID (N'thetype') IS NOT NULL
   PRINT 'the type existe'
ELSE
   PRINT 'No existe'
GO

sábado, 15 de junio de 2013

Procedimientos Almacenados Multi-parametros: Stament Dinámico vs Stament Predefinido

Aquellos que en nuestro desempeño como programadores de aplicaciones enlazadas a bases de datos nos enfrentamos al reto de la optimización de nuestros 'staments'. Todos en el algún momento hemos tenido la necesidad realizar consultas con parametros múltiples que puedan ser usados individualmente o combinados. ¿A que me refiero? Tomando como referencia la tabla Employee de la base de datos AdventureWorks supongamos que debemos crear un procedimiento almacenado que obtenga la información general de los empleados en base a los parámetros cargo (JobTitle), género (Gender) y fecha de contratación (HireDate), el procedimiento debe ser capaz de devolver un conjunto de resultados usando cada uno de los parámetros por separados o cada una de las combinaciones posibles para los parámetros a los que se proporcionó un valor válido o ninguno de ellos. La primera vez que se me presentó un escenario similar creé un procedimiento que crea la sentencia en linea evualuando los valores de los parámetros y concatenandolo a la sentencia base si el valor era válido. Este es un ejemplo:
USE AdventureWorks
GO
CREATE PROCEDURE uspObtenerEmpleadosv1 
@JobTitle NVARCHAR(50),
@Gender NCHAR(1),
@HireDate1 DATE,
@HireDate2 DATE
AS
DECLARE @Statement NVARCHAR(MAX), @Where NVARCHAR(500)

SET @Where = ''
SET @Statement = N'SELECT e.BusinessEntityID, e.NationalIDNumber, e.LoginID,
 e.OrganizationNode, e.OrganizationLevel, e.JobTitle,
 e.BirthDate, e.MaritalStatus, e.Gender,
 e.HireDate, e.SalariedFlag, e.VacationHours,
 e.SickLeaveHours, e.CurrentFlag, e.rowguid, e.ModifiedDate
FROM
 HumanResources.Employee e'
 
IF @JobTitle IS NOT NULL
 SELECT @Where = @Where + CASE @Where WHEN '' THEN ' WHERE e.JobTitle LIKE ''' + @JobTitle + ''''
       ELSE ' AND WHERE e.JobTitle LIKE ''' + @JobTitle + '''' END

IF @Gender IS NOT NULL
 SELECT @Where = @Where + CASE @Where WHEN '' THEN ' WHERE e.Gender = ''' + @Gender + ''''
       ELSE ' AND e.Gender = ''' + @Gender + '''' END
IF @HireDate1 IS NOT NULL AND @HireDate2 IS NOT NULL
  SELECT @Where = @Where + CASE @Where WHEN '' THEN ' WHERE e.HireDate BETWEEN ''' + CONVERT(VARCHAR(10),@HireDate1,111) + ''' AND ''' + CONVERT(VARCHAR(10),@HireDate2,111) + ''''
       ELSE ' AND (e.HireDate BETWEEN ''' + CONVERT(VARCHAR(10),@HireDate1,111) + ''' AND ''' + CONVERT(VARCHAR(10),@HireDate2,111) + ''')' END
ELSE
 IF @HireDate1 IS NOT NULL AND @HireDate2 IS NULL
  SELECT @Where = @Where + CASE @Where WHEN '' THEN ' WHERE e.HireDate = ''' + CONVERT(VARCHAR(10),@HireDate1,111) + ''''
       ELSE ' AND e.HireDate = ''' + CONVERT(VARCHAR(10),@HireDate1,111) + '''' END
       
SET @Statement = @Statement +  @Where;
EXECUTE sp_executesql @Statement;
GO
Con el paso del tiempo y conforme la base de datos creció me di cuenta que si bien este método cumplía la función para la que lo había creado el tiempo de respuesta era muy alto. Fue entonces cuando, gracias a la indicación de un compañero, pude darme cuenta que el problema radicaba en que por cada ejecución del procedimiento almacenado SQL Server tenía que compilar la consulta nuevamente, esto es innecesario y contraproducente a menos que el diseño de la(s) tabla(s) hayan sufrido cambios significativos. La idea de crear un procedimiento almacenado es, corrijanme si me equivoco, que al momento de la creación del mismo el motor de base de datos trace un plan de consulta(ejecución) reutilizable que disminuya el tiempo de respuesta; el método anterior tira por el suelo esa utilidad. Para remediar la situación mi compañero me planteó la alternativa que hoy comparto con ustedes:
USE AdventureWorks
GO

CREATE PROCEDURE uspObtenerEmpleadosv2
@JobTitle NVARCHAR(50) = NULL,
@Gender NCHAR(1) = NULL,
@HireDate1 DATE = NULL,
@HireDate2 DATE = NULL
AS
SELECT
 e.BusinessEntityID, e.NationalIDNumber, e.LoginID,
 e.OrganizationNode, e.OrganizationLevel, e.JobTitle,
 e.BirthDate, e.MaritalStatus, e.Gender,
 e.HireDate, e.SalariedFlag, e.VacationHours,
 e.SickLeaveHours, e.CurrentFlag, e.rowguid, e.ModifiedDate
FROM
 HumanResources.Employee e 
WHERE ((@JobTitle IS NULL) OR (e.JobTitle LIKE @JobTitle))
 AND ((@Gender IS NULL) OR (e.Gender = @Gender))
 AND ((@HireDate1 IS NULL AND @HireDate2 IS NULL) 
  OR ((@HireDate1 IS NOT NULL AND @HireDate2 IS NOT NULL) AND (e.HireDate BETWEEN @HireDate1 AND @HireDate2)) 
  OR (@HireDate1 IS NOT NULL AND e.HireDate = @HireDate1))
GO
Puede apreciarse que ahora la sentencia es única y predeterminada, por lo tanto el plan de ejecución trazado por el motor de base de datos durante la creación del procedimiento será utilizado tal cual y no deberá redefinirse. Añado dos sentencias de ejecución para que comparen el 'Plan de Ejecución Estimado' de una y otra versión
EXEC uspObtenerEmpleadosv1 @Title = N'Sales Representative' --NVARCHAR(50)
                          ,@Gender = N'F' --NCHAR(1)
                          ,@HireDate1 = '2003-01-01' --'YYYY-MM-DD' DATE
                          ,@HireDate2 = '2005-12-31' --'YYYY-MM-DD' DATE
GO
EXEC uspObtenerEmpleadosv2 @Title = N'Sales Representative' --NVARCHAR(50)
                          ,@Gender = N'F' --NCHAR(1)
                          ,@HireDate1 = '2003-01-01' --'YYYY-MM-DD' DATE
                          ,@HireDate2 = '2005-12-31' --'YYYY-MM-DD' DATE
GO
En conclusión, un paso adelante para mejorar el tiempo de respuesta de nuestros procedimientos almacenados es eliminar la construcción de sentencias t-sql en ellos. Agradezco sus comentarios constructivos. Saludos

sábado, 21 de julio de 2012

SQL Server trunca divisiones de enteros

Pues eso, revisando una función que entre otras acciones divide dos números enteros (entiéndase dos datos de tipo int)descubrí que sql server trunca el resultado. Aunque esto está claramente documentado en los libros en pantalla me tomó por asalto. Esto puede resultar una verdadera catástrofe para aquellos novatos o algunos con un poquito mas de experiencia que nos descuidamos.
DECLARE @dividendo int, @divisor int

set @dividendo = 50;
set @divisor = 12;

SELECT (@dividendo/@divisor)
GO
El resultado para esta simple operación no es el deseado, puesto que retorna 4 en lugar de 4.166
DECLARE @dividendo int, @divisor int

set @dividendo = 50;
set @divisor = 12;

SELECT (cast(@dividendo as decimal(18,6))/cast(@divisor as decimal(18,6)))
GO
Ahora si obtenemos el dato que realmente necesitamos.
Saludos.

domingo, 17 de junio de 2012

Como Hacer Reporte Crystal Reports y Asp .Net

Recientemente una amiga me pidio ayuda para hacer un reporte rápido con crystal reports y asp .net.

El ejemplo que hice para ella y que hoy comparto con usted hace uso del wizard de visual studio válido para las versiones 2005 y 2008. Sé que habrá quienes digan que esta no es la mejor manera de hacerlo, quienes lo hagan seguramente ya tienen experiencia en esto, sin embargo el ejemplo está dirigido para principiantes. En otra ocasión compartiré otro método. Por lo pronto acá les dejo el enlace del video en youtube y el código fuente:

Imports CrystalDecisions.Shared
Imports CrystalDecisions.CrystalReports.Engine
Partial Class _Default
    Inherits System.Web.UI.Page
    Private Sub configureCRYSTALREPORT()

        Dim myConnectionInfo As New ConnectionInfo()
        myConnectionInfo.ServerName = "NOMBRE_O_IP_DEL_SERVIDOR"
        myConnectionInfo.DatabaseName = "Northwind"
        myConnectionInfo.UserID = "sa"
        myConnectionInfo.Password = "myserver"
        setDBLOGONforREPORT(myConnectionInfo)

    End Sub

    Private Sub setDBLOGONforREPORT(ByVal myconnectioninfo As ConnectionInfo)

        Dim mytableloginfos As New TableLogOnInfos()
        mytableloginfos = CrystalReportViewer1.LogOnInfo

        For Each myTableLogOnInfo As TableLogOnInfo In mytableloginfos
            myTableLogOnInfo.ConnectionInfo = myconnectioninfo
        Next

    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        configureCRYSTALREPORT()
    End Sub
End Class

Me han solicitado el código en c#. Acá se los dejo.
using CrystalDecisions.Shared;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Web;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        configureCRYSTALREPORT();
    }

    private void configureCRYSTALREPORT()
    {
        ConnectionInfo myConnectionInfo = new ConnectionInfo();
        myConnectionInfo.ServerName = "NOMBRE_O_IP_DEL_SERVIDOR";
        myConnectionInfo.DatabaseName = "Northwind";
        myConnectionInfo.UserID = "sa";
        myConnectionInfo.Password = "mipassword";
        setDBLOGONforREPORT(myConnectionInfo);
 
    }
 
    private void setDBLOGONforREPORT(ConnectionInfo myconnectioninfo)
    {
        TableLogOnInfos mytableloginfos = new TableLogOnInfos();
        mytableloginfos = CrystalReportViewer1.LogOnInfo;
     
        foreach (TableLogOnInfo myTableLogOnInfo  in mytableloginfos)
        {
            myTableLogOnInfo.ConnectionInfo = myconnectioninfo;
        }
     
    }

}
Si desean usar la autenticacion de windows en lugar de autenticacion de sql server cambian las lineas correspondientes al UserID y Password por lo siguiente:
myConnectionInfo.IntegratedSecurity = true;
Para evitar que durante la publicación recuerde los datos de prueba deben activar la opción Discard Saved Data When Loading Reports en las imagenes siguientes muestro como hacerlo

Actualización:
Para Visual Studio 2010 y versiones posteriores es necesario descargar e instalar SAP Crystal Reports, developer version for Microsoft Visual Studio, despues de la instalación el procedimiento a seguir es el mismo.

Saludos.

domingo, 4 de marzo de 2012

Como fijar el precio de tu trabajo?

Ayer tuve una experiencia que me hizo recordar una historia que leí en la universidad hace algunos años. La historia dice así:

"Algunas veces es un error juzgar el valor de una actividad simplemente por el tiempo que toma realizarla… Un buen ejemplo es el caso del ingeniero que fue llamado a arreglar una computadora muy grande y extremadamente compleja… una computadora que valía 12 millones de dólares.
Sentado frente a la pantalla, oprimió unas cuantas teclas, asintió con la cabeza, murmuró algo para sí mismo y apagó el aparato. Procedió a sacar un pequeño destornillador de su bolsillo y dio vuelta y media a un minúsculo tornillo. Entonces encendió de nuevo la computadora y comprobó que estaba trabajando perfectamente. El presidente de la compañía se mostró encantado y se ofreció a pagar la cuenta en el acto. - ¿Cuánto le debo? -preguntó. - Son mil dólares, si me hace el favor. - ¿Mil dólares? ¿Mil dólares por unos momentos de trabajo? ¿Mil dólares por apretar un simple tornillito? ¡Ya sé que mi computadora cuesta 12 millones de dólares, pero mil dólares es una cantidad disparatada! La pagaré sólo si me manda una factura perfectamente detallada que lo justifique. El ingeniero asintió con la cabeza y se fue. A la mañana siguiente, el presidente recibió la factura, la leyó con cuidado, sacudió la cabeza y procedió a pagarla en el acto, sin chistar.
La factura decía:
Servicios prestados:
Apretar un tornillo:…………. 1 dólar 
Saber qué tornillo apretar: 999 dólares
Total:………………………… 1.000 dólares"


Por qué lo recordé? Pues porque me sucedió lo mismo. Fui llamado de emergencia para atender un problema con un servidor de una empresa X. Hice mi trabajo en un tiempo relativamente corto. Desafortunadamente al momento de cobrar mis clientes no fueron tan comprensivos como el presidente de la compañía de la historia. El mundo real es así, existen empresarios a los que poco les importa el tiempo, dinero y esfuerzo invertido en tu preparación profesional, ellos quieren poner precio a tu trabajo.

Todo aquel que, al igual que yo, realiza trabajos como FreeLancer enfrenta este tipo situaciones. Y nos hacemos la pregunta ¿Como debemos fijar el precio de nuestro trabajo? Personalmente escojo de entre las siguientes opciones:

  • Fijar el precio antes de hacer el trabajo. Es recomendable cuando realizas un trabajo "mecánico". Es decir tareas para las que las soluciones son obvias, o que no necesariamente involucran solucionar algún problema. Ejemplo de esto pueden ser el mantenimiento preventivo de pc, instalación y/o actualización de software, configuraciones básicas del s.o, instalación de hardware, cableado de red, etc. 
    • Ventaja: Si el cliente no acepta tu precio puedes dar media vuelta e irte sin mover un solo dedo.
    • Desventaja: Corres el riesgo de encontrarte con una situación imprevista  que demande mucho mas tiempo y esfuerzo que no serán remunerados.
  • Realizar un diagnostico, luego fijar el precio. Aconsejo este método para casos en los que "hay un problema" y según nuestra experiencia sabemos de manera precisa las posibles soluciones a aplicar.
    • Ventaja: Fijarás un precio con el que al final estarás satisfecho.
    • Desventaja: El diagnostico "ya es trabajo", es esfuerzo y tiempo que no será remunerado si el cliente decide no hacer uso de tus servicios.
Personalmente esta es la opción que uso cuando el encargo es el desarrollo de alguna aplicación o diseño de sitio web. Ustedes ya sabrán que primero es el análisis de requerimientos. En este ultimo ejemplo lo ideal es dejar el acuerdo por escrito.
  • Realizar todo el trabajo luego fijar el precio. Si, si... está ultima opción es la que llevo a escribir este artículo. Sin embargo hay ocasiones en las que el pc o servidor presenta comportamiento  tipo poltergeist. Esas ocasiones en las que toda tu experiencia acumulada no es suficiente para explicar de forma precisa el mal funcionamiento del hardware o software. Entonces tienes que recurrir a probar múltiples opciones de solución o invocar al CHapulin Colorado, jeje. Al final lo justo es cobrar por todo el esfuerzo y tiempo invertido y no solo por la solución que a fin cuentas funcionó.
Cualquiera de estas opciones las aplico considerando los precios en el mercado para cada servicio procurando siempre un precio justo para el cliente y para mí.

Esta es una humilde apreciación, y tu que opinas?

P.D.: Agradezco los comentarios constructivos.

domingo, 19 de febrero de 2012

Cerrar Todas Las Conexiones A Base De Datos SQL Server

Hola amigos. Hace algún tiempo un amigo me compartió este script que encontró en SQLServerCentral. Su finalidad es cerrar todas las conexiones existentes a una base de datos SQL Server. Solamente necesitas pasarle como parámetro el nombre de la base de datos. Lo he aplicado en SQL Server 2005 y funciona sin problemas.
Aunque no he trabajado como DBA en mi trabajo como desarrollador me ha sido de mucha utilidad. Espero le s sea útil a ustedes también.


/*

* Author Rafal Skotak

* Purpose Procedure tries to kill all connections to the specified database

*

* Date 2011.02.18

*

******************************************************************************************************/

USE [master]

GO

CREATE PROCEDURE proc_kill_dbconnections

@dbname SYSNAME

with encryption

as

begin

set nocount on



------- prepare temporary tables

-------------------------------------------

create table #temp_processes_table

(

spid int primary key,

ecid int,

status sysname,

loginame sysname null,

hostname sysname null,

blk int,

dbname sysname null,

cmd sysname null,

request_id int null,

mod int not null default 0

);



-- get processes list

--------------------------

insert into #temp_processes_table (spid, ecid, status, loginame, hostname, blk, dbname, cmd, request_id)

exec sp_who



declare @count int

declare @process_id int

declare @cmd varchar(8000)



select @count = count(*) from #temp_processes_table where mod = 0



while @count > 0

begin

set @process_id = NULL



select top 1 @process_id = spid from #temp_processes_table where mod = 0 and dbname = @dbname



if @process_id is NULL

break



print @process_id



set @cmd = 'kill ' + cast(@process_id as varchar)



exec (@cmd)



update #temp_processes_table set mod = 1 where spid = @process_id



select @count = count(*) from #temp_processes_table where mod = 0 and dbname = @dbname

end



-- cleanup - drop temporary tables

---------------------------------------

drop table #temp_processes_table

end

go



-- example



-- exec dbo.proc_kill_dbconnections 'dbCuentasxCobrar'

domingo, 4 de diciembre de 2011

DICCIONARIO DE DATOS CON SQL SERVER

En internet hay varios ejemplos que abordan este tema sin embargo ninguno de ellos me ha terminado de gustar, de manera rapida escribo este script que hace el trabajo que 'por ahora' requiero. En otra oportunidad profundizaré mas en el tema. Para este ejemplo hago uso basicamente de: INFORMATION_SCHEMA.TABLES para obtener informacion de las tablas de la bd e INFORMATION_SCHEMA.COLUMNS para obtener informacion de cada columna por cada tabla. Sin mas preambulo aca el script:
SET NOCOUNT ON

DECLARE @NOMBRE_TABLA sysname
DECLARE @iColumna int,@iUltimaColumna int,
  @NombreColumna sysname,
  @TipoColumna sysname,
  @Longitud int

SELECT @NOMBRE_TABLA = MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE= 'BASE TABLE' AND TABLE_NAME!= 'sysdiagrams'

WHILE @NOMBRE_TABLA IS NOT NULL
BEGIN
 PRINT '======================' + @NOMBRE_TABLA + ':========================='
 
 SELECT @iColumna = 0, @iUltimaColumna = max(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @NOMBRE_TABLA

 WHILE @iColumna < @iUltimaColumna
 BEGIN
  SELECT @iColumna = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = @NOMBRE_TABLA and ORDINAL_POSITION > @iColumna;
  --
  SELECT @NombreColumna = IC.COLUMN_NAME, @TipoColumna = IC.DATA_TYPE,  
  @Longitud = CASE WHEN CAST(IC.DATA_TYPE AS NVARCHAR) IN ('char','nchar','varchar','nvarchar','text','ntext') THEN IC.CHARACTER_MAXIMUM_LENGTH ELSE NULL END
  FROM INFORMATION_SCHEMA.COLUMNS IC
  WHERE IC.TABLE_NAME = @NOMBRE_TABLA and IC.ORDINAL_POSITION = @iColumna;
  PRINT '. '+ @NombreColumna +  ' ' + @TipoColumna + ' ' + ISNULL(CAST(@Longitud AS NVARCHAR(64)),'')
  --
 END
 
 SELECT @NOMBRE_TABLA= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME> @NOMBRE_TABLA
 AND TABLE_TYPE= 'BASE TABLE' AND TABLE_NAME!= 'sysdiagrams'
END