September 19, 2023 at 10:01 am
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
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply