While looking for a solution of a different problem, I realized that SQL Server has a feature called extended stored procedures. Extended stored procedures, at least in SQL Server 2000, have to be written in C++ and compiled to a DLL. A good example of such a procedure is my previous article on Using Regular Expression in SQL Server. I am not going to get into how to write extended stored procedures in this article since my C++ skills are rusty at best. Instead, I will talk about using an existing stored procedure to query the file system and how to move from using ColdFusion to using SQL Server.
Getting a list of files is pretty trivial in ColdFusion. All you have to do is use the "<cfdirectory>" tag like so:
<cfset localDirectoryPath = "C:\Temp" /> <cfdirectory name="textFiles" action="list" filter="*.txt" directory="#localDirectoryPath#" />
That will get a list of all files with the ".txt" extension in the "C:\Temp" directory.
Getting a list of files from a network drive is not any different except for specifying the networks path as UNC path (and a little service configuration):
<cfset networkDirectoryPath = "\\larry\share" /> <cfdirectory name="textFiles" action="list" filter="*.txt" directory="#networkDirectoryPath#" />
As stated above, this will not work if you do not have the ColdFusion service configured to use a specific account that has access to your network path.
The typical ColdFusion service setup looks like the following screen shot when accessed through the "Services" configuration in Windows:
To be able to query a network path, you need to specify a user that has access to that network path:
Using SQL Server
The setup for using SQL Server for querying network paths is the same as the one for ColdFusion:
If you do not setup the SQL Server to run as the appropriate user, trying to query a network path with the method provided here, you will an access denied error:
With that aside, I have written a custom stored procedures based on the built-in extended stored procedure "xp_cmdshell". This procedure will return a query with the following:
- fileID - unique auto incremented integer value
- fileName - the name of the file
- lastModifiedOn - the date the file was last modified
- fileSize - the size of the file in bytes
Here are the results of querying the local path "C:\Temp" without a file extensions filter:
exec dbo.getDirectoryFileList 'c:\temp', null
Once configured the procedure can also work on network drives like so to return only files with the ".btw" extension:
exec dbo.getDirectoryFileList '\\larry\indium\Production\labelGeneration', '*.btw'
Show Me the Code
/* Type: Stored Procedure Name: dbo.getDirectoryFileList Author: Boyan Kostadinov Created: 03.17.2008 Dependencies: master.dbo.xp_cmdshell Usage: exec dbo.getDirectoryFileList 'c:\temp', null exec dbo.getDirectoryFileList '\\larry\indium\Production\labelGeneration', '*.btw' Parameters: @directoryPath varchar(255) - The path of the local or network directory @fileExtensionFilter varchar(10) - Optional - The file extension to filter the file list by Returns: A list of files found on the file system */ create procedure dbo.getDirectoryFileList @directoryPath varchar(255), @fileExtensionFilter varchar(10) = null as set nocount on -- Declare and initialize local variables declare @dosCommand varchar(5000) set @dosCommand = '' -- If the file extension fileter was empty, set it to all files if @fileExtensionFilter is null or ltrim(rtrim(@fileExtensionFilter)) = '' set @fileExtensionFilter = '*.*' -- If the directory path does not have an ending '\', append one if substring(@directoryPath, len(@directoryPath), 1) <> '\' set @directoryPath = @directoryPath + '\' -- Build the dos command to get a list of files select @dosCommand = 'insert into #tempFileList(fileListRow) ' + 'exec master.dbo.xp_cmdshell ''dir ' + @directoryPath + + @fileExtensionFilter + '''' -- Create a temporary table to store the file list create table #tempFileList ( fileListRow varchar(1000) null ) -- Create the #fileList temporary table to store the file list create table #fileList ( fileID int primary key identity(1,1) not null, [fileName] varchar(255) not null, lastModifiedOn datetime not null, fileSize bigint not null, ) exec(@dosCommand) -- 8 - Delete unneeded data from the #OriginalFileList delete from #tempFileList where fileListRow is null delete from #tempFileList where fileListRow like '%Volume%' delete from #tempFileList where fileListRow like '%Directory%' delete from #tempFileList where fileListRow like '%<DIR>%' delete from #tempFileList where fileListRow like '%bytes%' if not exists (select * from #tempFileList where fileListRow like '%access is denied%') begin -- Populate the #fileList table with the final data insert into #fileList(lastModifiedOn, fileSize, [fileName]) select ltrim(substring(fileListRow, 1, 10)) + ' ' + rtrim(ltrim(substring(fileListRow, 11, 15))) + 'm' as 'lastModifiedOn', replace(ltrim(substring(fileListRow, 21, 18)), ',', '') as 'fileSize', ltrim(substring(fileListRow, 40, 1000)) as 'fileName' from #tempFileList select * from #fileList end else select fileListRow as errorMessage from [#tempFileList] as e -- Drop the temporary tables drop table #tempFileList drop table #fileList set nocount off go
You can download the stored procedure from http://tech-cats.net/blog/downloads/sql/getDirectoryFileList.txt