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