473,324 Members | 2,239 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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

Jul 23 '05 #1
5 20915
(jp*****@gmail.com) writes:
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.


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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
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

Jul 23 '05 #3
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.

Jul 23 '05 #4
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

Jul 23 '05 #5
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.

Jul 23 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Harold Crump | last post by:
Greetings, I need to get a list of all files in a particular virtual directory on a web site, such as - http://www.myServer.com/FileStore I tried using the directory functions, but this...
8
by: RML | last post by:
hey guys, i am looking at this piece of code that lists numbers of files in a directory. i want to convert it so it lists the files in th directory that end with .doc. i cant seem to get it to...
3
by: Nick | last post by:
Is it possible to read a list of files from a specified directory using VB.net We have company intranet and I have created a page that displays photos from different events. I have coded a page...
11
by: Madison Kelly | last post by:
Hi all, I am new to the list and I didn't want to seem rude at all so I wanted to ask if this was okay first. I have a program I have written in perl which uses a postgresSQL database as the...
2
by: Rob_S | last post by:
I have a program which saves time stamped files into time stamped directories. When I want to read these files, I get the current date and check for the existence of the directory using.... ...
7
by: Kristan | last post by:
Hi there, quick question, how would I retrieve a list of files in ANSI C in a purely platform independent way? Any pointers would be great! thanks Kristan
4
by: thoseion | last post by:
Hi, I am trying to get a program working whereby directory and file names are read into a list. I have been given the original list structure - it appears that the directory names should be added...
6
by: tgnelson85 | last post by:
Hello, C question here (running on Linux, though there should be no platform specific code). After reading through a few examples, and following one in a book, for linked lists i thought i would...
3
by: froditus | last post by:
Hello everyone, is it possible to list files from directory other than in apache web directory? my web folder is placed on c:/ and i put my files in windows directory "d:/files/images/". I...
9
by: kkhan5000 | last post by:
Hello, I am new to PHP and have put together the following code just by copying other examples from the web. Now I am stuck and would like to sort and display files alphabetically, and also filter...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.