I found one method that use's xp_cmdshell but I can't use as we have our db locked down, anyone have and other suggestions?
USE master
GO
CREATE PROCEDURE dbo.sp_ListFiles
@PCWrite varchar(2000),
@DBTable varchar(100)= NULL,
@PCIntra varchar(100)= NULL,
@PCExtra varchar(100)= NULL,
@DBUltra bit = 0
AS
SET NOCOUNT ON
DECLARE @Return int
DECLARE @Retain int
DECLARE @status int
SET @status = 0
DECLARE @Task varchar(2000)
DECLARE @Work varchar(2000)
DECLARE @Wish varchar(2000)
SET @Work = 'DIR ' + '"' + @PCWrite + '"'
CREATE TABLE #DBAZ (Name varchar(400), Work int IDENTITY(1,1))
INSERT #DBAZ EXECUTE @Return = master.dbo.xp_cmdshell @Work
SET @Retain = @@ERROR
IF @status = 0 SET @status = @Retain
IF @status = 0 SET @status = @Return
IF (SELECT COUNT(*) FROM #DBAZ) < 4
BEGIN
SELECT @Wish = Name FROM #DBAZ WHERE Work = 1
IF @Wish IS NULL
BEGIN
RAISERROR ('General error [%d]',16,1,@Status)
END
ELSE
BEGIN
RAISERROR (@Wish,16,1)
END
END
ELSE
BEGIN
DELETE #DBAZ WHERE ISDATE(SUBSTRING(Name,1,10)) = 0 OR SUBSTRING
(Name,40,1) = '.' OR Name LIKE '%.lnk'
IF @DBTable IS NULL
BEGIN
SELECT SUBSTRING(Name,40,100) AS Files
FROM #DBAZ
WHERE 0 = 0
AND (@DBUltra = 0 OR Name LIKE '% %')
AND (@DBUltra != 0 OR Name NOT LIKE '% %')
AND (@PCIntra IS NULL OR SUBSTRING(Name,40,100) LIKE @PCIntra)
AND (@PCExtra IS NULL OR SUBSTRING(Name,40,100) NOT LIKE @PCExtra)
ORDER BY 1
END
ELSE
BEGIN
SET @Task = ' INSERT ' + REPLACE(@DBTable,CHAR(32),CHAR(95))
+ ' SELECT SUBSTRING(Name,40,100) AS Files'
+ ' FROM #DBAZ'
+ ' WHERE 0 = 0'
+ CASE WHEN @DBUltra = 0 THEN '' ELSE ' AND Name LIKE ' + CHAR(39) + '% %' + CHAR(39) END
+ CASE WHEN @DBUltra != 0 THEN '' ELSE ' AND Name NOT LIKE ' + CHAR(39) + '% %' + CHAR(39) END
+ CASE WHEN @PCIntra IS NULL THEN '' ELSE ' AND SUBSTRING (Name,40,100) LIKE ' + CHAR(39) + @PCIntra + CHAR(39) END
+ CASE WHEN @PCExtra IS NULL THEN '' ELSE ' AND SUBSTRING
(Name,40,100) NOT LIKE ' + CHAR(39) + @PCExtra + CHAR(39) END
+ ' ORDER BY 1'
IF @status = 0 EXECUTE (@Task) SET @Return = @@ERROR
IF @status = 0 SET @status = @Return
END
END
DROP TABLE #DBAZ
SET NOCOUNT OFF
RETURN (@Status)
GO
And to test:
EXECUTE sp_ListFiles 'C:\Documents and Settings\All Users\Desktop\FolderName',NULL,NULL,NULL,1