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