Get list of files in directory using a SP

Is there an XP/SP out there that will return a list of files residing
in a specified directory?

I'm looking for something simlar to

Execute master..xp_subdirs N'C:\'

But instead of it returning a list of subdirs I want it to return a
list of files in that directory.

Jeff

re: Get list of files in directory using a SP

(jpasqua@gmail.com) writes:[color=blue]
> Is there an XP/SP out there that will return a list of files residing
> in a specified directory?
>
> I'm looking for something simlar to
>
> Execute master..xp_subdirs N'C:\'
>
> But instead of it returning a list of subdirs I want it to return a
> list of files in that directory.[/color]

Would

exec master..xp_cmdshell 'DIR /b C:\'

do?

There are some other extended stored procedures that might to the job,
for instance xp_dirtree. But these are undocuemnted and supported - as
are xp_subdirs - and they could go in smoke with the next version of
SQL Server


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

re: Get list of files in directory using a SP

Erland,

I gave that a shot but unfortunately I don't have permissions to run
that SP. Basically I need to do this as part of a DTS package. I've
developed an alternative method where I use a JScript (using the
Scripting.FileSystemObject) to export the list of files in the
directory into a .txt file, then import the txt file into my SQL server
table using a transform data task.

Is there a way to have the JS file directly append its results to the
sql server without using the .txt file as a middle man?

Thanks

re: Get list of files in directory using a SP

This sounds like something that an ActiveX Script would be more
appropriate for, rather than using SQL. Get the file names using the
FileSystemObject as you did, but instead of putting them into a text
file, use an ADO object to insert them into the database.

HTH,
-Tom.

re: Get list of files in directory using a SP

Ok, I've got the file names using FileSystemObject. Any chance you can
point me in the right direction about how to go about inserting them
using ADO w/ vbscript?

Jeff

re: Get list of files in directory using a SP

There are a few ways that you can do it - stored procedure, execute
direct insert statements, or use ADO's built in methods. I'm not really
a VB programmer, but using the built in methods I believe that the
following should put you in the right direction:

Dim conSQL
Dim rsFiles
Dim strConnectionString, strFileName

Set conSQL = Server.CreateObject("ADODB.Connection")
' This connection string will vary based on your server, database name,
etc.
strConnectionString = "Provider='sqloledb';Data Source=" & _
Request.ServerVariables("SERVER_NAME") & ";" &
_
"Integrated Security='SSPI';Initial
Catalog='Northwind';"

conSQL.Open strConnectionString

Set rsFiles = Server.CreateObject("ADODB.Recordset")

rsFiles.ActiveConnection = conSQL
rsFiles.CursorLocation = adUseClient
rsFiles.CursorType = adOpenKeyset
rsFiles.LockType = adLockOptimistic
rsFiles.Source = "Files" ' This is your table name
rsFiles.Open

*** Loop through your file names and put the file name in the
strFileName variable ***
rsFiles.AddNew
rsFiles("file_name") = strFileName ' Set all of your other column
values the same way
*** End of your loop ***

rsFiles.Update ' This will save all of your records


As I said, I'm not really a VBScript programmer, I haven't tested this
code, and it has no error handling, etc. Check out the Microsoft
website, ADO newsgroups or websites, etc. for a better reference.

HTH,
-Tom.