viernes, 22 de julio de 2016

SQL Server: inhabilitar trigger temporalmente

ALTER TABLE Certificados DISABLE TRIGGER Certificados_Trigger_Eventos
GO
UPDATE Certificados
SET Resolución = 'blabla'
GO
ALTER TABLE Certificados ENABLE TRIGGER Certificados_Trigger_Eventos
GO

martes, 19 de julio de 2016

SQL Server: leer contenido de una carpeta de Windows

Escenario

Se requiere que un stored procedure acceda a una carpeta de windows y lea los nombres de los archivos dentro de esa carpeta

Enfoque xp_dirtree

La siguiente línea de código funciona:

EXEC xp_dirtree @carpeta, 1, 1 

Pero posee algunas desventajas
  • Estos procedimientos no están documentados
  • Microsoft recomienda no usarlos
  • Microsoft aclara que pueden desaparecer en versiones futuras de SQL Server
  • Requiere permisos de sysadmin para ver el contenido de la carpeta

Enfoque CLR

Todas las recomendaciones indican no utilizar los procedimientos XP y en su lugar crear código C# que pueda ser utilizado desde SQL Server. Para ello se requiere crear un proyecto especial en Visual Studio para SQL Server.

El código para leer los archivos es el publicado en http://stackoverflow.com/a/11560496/2144424 por Eoin Campbell:

public partial class UserDefinedFunctions
{
    [SqlFunction(DataAccess = DataAccessKind.Read,
        FillRowMethodName = "GetFiles_FillRow", TableDefinition = "FilePath nvarchar(4000)")]
    public static IEnumerable GetFiles(SqlString path)
    {
        return System.IO.Directory.GetFiles(path.ToString()).Select(s => new SqlString(s));
    }

    public static void GetFiles_FillRow(object obj,out SqlString filePath)
    {
        filePath = (SqlString)obj;
    }
};

Una vez desplegada esta solución, veremos lo siguiente en SQL Server:


Y funciona simplemente haciendo lo siguiente

select * From GetFiles('C:\Temp\');
En mi caso, como he incluido este SELECT dentro de un sotored procedure, he necesitado darle más privilegios al SP usando esta línea de código:

CREATE PROCEDURE dbo.EnviarMails
@auditoria INT,
@modo NVARCHAR(10)
WITH EXECUTE AS OWNER
AS
DECLARE @para VARCHAR(MAX)

Por último muestro cómo hacer el despliegue de esta solución en ambientes productivos. Pueden ayudarse siguiendo este camino:
  • Despliegan desde Visual Studio en el ambiente de desarrollo
  • Utilizan en SQL Management Studio la función de generar scripts para crear el script de instalación en producción
De todas formas, acá dejo un ejemplo:

USE [mibase]
GO
EXEC sp_configure 'clr enabled', '1'
GO
RECONFIGURE
GO
EXEC sp_changedbowner 'sa'
GO
ALTER DATABASE mibase SET TRUSTWORTHY ON;
GO
IF EXISTS (SELECT *
           FROM   sys.objects
           WHERE  object_id = OBJECT_ID(N'[dbo].[GetFiles]')
                  AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
DROP FUNCTION [GetFiles]
GO
IF EXISTS (
SELECT * 
FROM sys.assemblies 
WHERE name='BD_Extended'
)
BEGIN
DROP ASSEMBLY BD_Extended
END
GO
CREATE ASSEMBLY _Extended from 'C:\BD_Extended\BD.Extended.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
CREATE FUNCTION [dbo].[GetFiles](@path [nvarchar](4000))
RETURNS  TABLE (
[FilePath] [nvarchar](4000) NULL
) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [BD_Extended].[UserDefinedFunctions].[GetFiles]
GO

jueves, 14 de julio de 2016

@@IDENTITY con MS Access y SQL Server

Problema

Supongamos un escenario en donde tenemos a SQL Server como base de datos y MS Access como aplicación.

Insertamos un registro en una tabla y queremos ver el ID asignado, para lo cual utilizamos esta línea de código:

Set rs = CurrentDb.OpenRecordset("SELECT @@IDENTITY", dbOpenForwardOnly)

¿Cuál es el problema?

Si existe un trigger detrás de esta tabla que inserta en otra tabla, es posible que el IDENTITY sea el del trigger y no el que estamos buscando

Solución

Una posible solución es usar la función nativa de SQL Server para obtener el último IDENTITY de una tabla:

Function Retornar_Identity_Tabla()
    Dim qdef As DAO.QueryDef
    Dim VarRecords As Variant
    Set qdef = CurrentDb.CreateQueryDef("")
    qdef.ReturnsRecords = True
    qdef.Connect = CurrentDb.TableDefs("dbo_Certificados").Connect
    qdef.sql = "SELECT IDENT_CURRENT('certificados')"
    qdef.OpenRecordset
    VarRecords = qdef.OpenRecordset.GetRows(1)
    Retornar_Scope_Identity = VarRecords(0, 0)
End Function

martes, 12 de julio de 2016

SQL SERVER: crear o reemplazar tabla

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'mitabla')
DROP TABLE mitabla
GO
CREATE TABLE [dbo].[mitabla](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Fecha] [datetime] NOT NULL,
[Aplicacion] [nvarchar](200) NOT NULL,
[Version] [nvarchar](50) NOT NULL,
[Titulo] [nvarchar](200) NOT NULL,
[Modulo] [nvarchar](200) NOT NULL,
[Usuario] [nvarchar](200) NULL,
[Detalle] [nvarchar](max) NULL,
[Severidad] [nvarchar](50) NULL,
CONSTRAINT [PK_mitabla] PRIMARY KEY
(
[ID]
)