October 16, 2013 at 8:05 am
DECLARE @path varchar(500)
SET @path = 'C:\MyFolder\MyFile.txt'
DECLARE @result INT
EXEC master.dbo.xp_fileexist @path, @result OUTPUT
SELECT @result
The code above enables me to determine if a file exists.
From within a stored procedure, can you count the number of files in 'MyFolder'?
October 16, 2013 at 8:46 am
How about using dir?
(untested code)
DECLARE @cmd nvarchar(500)
SET @cmd = 'dir C:\MyFolder\'
CREATE TABLE #DirOutput(
files varchar(500))
INSERT INTO #DirOutput
EXEC master.dbo.xp_cmdshell @cmd
SELECT *
FROM #DirOutput
October 16, 2013 at 9:30 am
Thanks for your reply. I tried it ... C:\MyFolder has one file in it.
The temp table ended up with one column called 'Files' with 11 rows in it showing ...
Volume in drive C is OS
Volume Serial Number is 1234-1ABC
null
Directory of C:\MyFolder
null
and so on ...
One of the rows contains '1 File(s) 11 bytes'
but I just want how many files are in that directory and nothing else.
October 16, 2013 at 9:44 am
I guess you're lacking of imagination. You just needed to adjust the temp table query.
DECLARE @cmd nvarchar(500)
SET @cmd = 'dir C:\'
CREATE TABLE #DirOutput(
files varchar(500))
INSERT INTO #DirOutput
EXEC master.dbo.xp_cmdshell @cmd
SELECT CAST( LEFT( files, PATINDEX('%File(s)%', files) - 1) AS int)
FROM #DirOutput
WHERE files LIKE '%File(s)%'
DROP TABLE #DirOutput
October 16, 2013 at 9:48 am
Just another option using dir parameters.
DECLARE @cmd nvarchar(500)
SET @cmd = 'dir C:\ /A:A'
CREATE TABLE #DirOutput(
files varchar(500))
INSERT INTO #DirOutput
EXEC master.dbo.xp_cmdshell @cmd
SELECT COUNT(*)
FROM #DirOutput
WHERE files LIKE '[0-9][0-9]/%'
DROP TABLE #DirOutput
October 16, 2013 at 10:12 am
Thanks again for your help. I found this in the mean time which does the job too.
IF OBJECT_ID('#DirTree') IS NOT NULL
DROP TABLE #DirTree
CREATE TABLE #DirTree (
SubDirectory nvarchar(255),
Depth smallint,
FileFlag bit
)
INSERT INTO #DirTree (SubDirectory, Depth, FileFlag)
EXEC xp_dirtree 'C:\MyFolder', 1, 1
SELECT COUNT(*) FROM #DirTree
IF OBJECT_ID('#DirTree') IS NOT NULL
DROP TABLE #DirTree
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy