Post reply

Alternatives to run EXEC from inside a table function

  • September 19, 2023 at 10:01 am

    #4289510

    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 
    (
    @Path NVARCHAR(MAX)
    )
    RETURNS @directory Table
    (
    subdirectory NVARCHAR(MAX)
    ,depth INT
    ,[file] INT
    )
    --RETURNS TABLE
    AS 
    BEGIN

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

    DECLARE  @tt TABLE
    (
    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
    END

    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.

  • September 19, 2023 at 1:37 pm

    #4289644

    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.

    ps With Later versions of SQL2017, and above, thte following might be worth looking at:

    SELECT *
    FROM sys.dm_os_enumerate_filesystem(@Path, 'x');

    • This reply was modified 1 year, 2 months ago by  Ken McKelvey.
    • This reply was modified 2 months, 3 weeks 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