Alternatives to run EXEC from inside a table function

  • Guys,

    I'm looking for some design advice.  I am trying to get a directory listing into T-SQL and due to current design constraints I need to do this within a UDF.  For security reasons OPENROWSET is not available to me and there is almost zero chance of getting it activated (recent cyber incident has the organisation paranoid).

    I can get the directory listing using EXEC xp_dirtree and I need to pass in the path string as an argument to the function.

    CREATE FUNCTION pow.tfn_ListFiles 
    RETURNS @directory Table
    subdirectory NVARCHAR(MAX)
    ,depth INT
    ,[file] INT

    --DECLARE @Path NVARCHAR(MAX) = '\\remote-filepath\d$\testfolder\testsubfolder'

    subdirectory NVARCHAR(MAX)
    ,depth INT
    ,[file] INT

    SET @SQL = 'EXEC xp_dirtree N''' + @Path + ''',2,1'

    INSERT INTO @tt EXEC sp_executeSQL @SQL
    --SELECT * FROM OPENROWSET('MSDASQL','DRIVER={SQL Server}; SERVER=localhost; Trusted_Connection=yes', 'EXEC xp_dirtree N'' + @Path + '',2,1')
    INSERT INTO @directory
    SELECT * FROM @tt

    This version of the code gives me the error message

    Invalid use of a side-effecting operator 'INSERT EXEC' within a function.

    If I try to RETURN TABLE and run the OPENROWSET I get the error message

    SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource'

    I know I can do it from inside a stored procedure, but this will require a fundamental redesign of the application structure which I desperately want to avoid.

    Anyone got any ideas how I can get around this limitation.

  • Anyone got any ideas how I can get around this limitation.

    You can use the CLR. The paid version of the following has File_GetDirectoryListing .

    SQL# (SQLsharp) - Expanding the capabilities of T-SQL via SQLCLR

    Personally, I would rather do this on the application server and pass the result to sql server.

    • This reply was modified 1 week, 4 days ago by  Ken McKelvey.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply