Over a million developers have joined DZone.

Querying the File System - From ColdFusion to SQL Server

· Database Zone

Learn NoSQL for free with hands-on sample code, example queries, tutorials, and more.  Brought to you in partnership with Couchbase.

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.

Using ColdFusion

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" />

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" />

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:

Default Setup sqlServerServiceNoUser But should be something like sqlServerServiceUser

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

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,


-- 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%')
-- 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)))
as 'lastModifiedOn',
replace(ltrim(substring(fileListRow, 21, 18)), ',', '') as 'fileSize',
ltrim(substring(fileListRow, 40, 1000)) as 'fileName'
from #tempFileList

select * from #fileList
select fileListRow as errorMessage from [#tempFileList] as e

-- Drop the temporary tables
drop table #tempFileList
drop table #fileList

set nocount off


You can download the stored procedure from http://tech-cats.net/blog/downloads/sql/getDirectoryFileList.txt


Accessing the Windows File System from SQL Server

The Getting Started with NoSQL Guide will get you hands-on with NoSQL in minutes with no coding needed. Brought to you in partnership with Couchbase.


Published at DZone with permission of Boyan Kostadinov, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}