Alternatives to run EXEC from inside a table function

• aaron.reese

  SSChampion

  Points: 13498

  More actions

  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.

• Ken McKelvey

  SSCoach

  Points: 18896

  More actions

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