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