Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Database of filenames in a directory

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-12 : 09:07:37
John S. writes "Hi,

Is there an easy (or any) way to create a table of filenames from a particular directory, then update the database automatically as the files in the directory change (files added nad/or removed)? I want to be able to search on filenames, using the data in the filenames table. We are running SQL Enterprise Server 2000.

Any assistance would be appreciated!

John"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-12 : 09:24:10
If you have the permissions to run xp_cmdshell, this is pretty easy to do. Set up a table like this one:

CREATE TABLE FileNames (FileName varchar(255) NOT NULL PRIMARY KEY)

Then create a scheduled job that runs these commands:

DELETE FROM FileNames
INSERT INTO FileNames EXECUTE xp_cmdshell 'dir/b C:\mypath'


That will fill the table with all the files (the /b flag removes the headers from the dir output)

This won't update automatically when a file is added, but you can schedule the INSERT job to run at periodic intervals (every minute, every 5 minutes, once a day, etc.) to refresh the file list.

Edited by - robvolk on 04/12/2002 09:24:38
Go to Top of Page

goheat
Starting Member

4 Posts

Posted - 2002-04-12 : 16:07:03
Thanks for the help, robvolk, but I get the following error when I try to run the INSERT command:

'Server: Message 515, Level 16, State 2, Line 1: Cannot insert the value NULL into column FileName. Column does not allow Nulls. Insert Failed'

When I just run the EXECUTE xp_cmdshell 'dir /b C:\files' part in Query Analyzer, it returns a grid with the proper files, but puts a NULL in the last row of the grid, which is probably causing the error. Any ideas on why or how to avoid the NULL?

Thanks!

John S.

Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-12 : 16:36:11
Use the following table definition, that'll fix it:

CREATE TABLE FileNames (FileName varchar(255) NULL)

Go to Top of Page

goheat
Starting Member

4 Posts

Posted - 2002-04-15 : 10:12:08
Thanks, robvolk! That worked perfectly! I really appreciate your help on this!

John

Go to Top of Page

goheat
Starting Member

4 Posts

Posted - 2002-04-25 : 10:44:53
FOLLOW-UP
This is working for me, but is there anyway to get the file creation date added to the SQL table using this method? The output could then show the filename and date.

Thanks!

Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-25 : 10:48:23
You can remove the /b switch from the dir command, but then you'll get a lot of header information that you have to remove later.

Run this in a command-line: dir/? and see if there are any additional switches that give you the information you need.

Go to Top of Page

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-04-25 : 11:33:29
This script will get you the filename, size, and last modified date.


set nocount on

create table #NewFiles ([FileName] varchar(255) null)

declare @cmd varchar(255) , @directory varchar(255)
select @directory = 'c:\'
--select @directory = '\\MyServer\MyShare\MyDirectory' -- will work wil UNC names
select @cmd = 'dir "' + @directory + '" /o:n /a:-d'

insert #NewFiles ( [FileName] )
exec master..xp_cmdshell @cmd

-- delete existing records
-- uncomment next line to do the deletion
--DELETE FROM FileNames

-- assumes that table FileNames has these fields: FileName varchar(255) , Size BigInt , Modified datetime
-- uncomment next line to do the insert
--INSERT INTO FileNames
select [FileName] = RTrim(SubString([FileName], 40, 255))
, [Size] = Convert(BigInt, Replace(SubString([FileName], 20, 20), ',', ''))
, Modified = convert(datetime, Replace(left([FileName], 18), ' ', ' ') + 'm')
from #NewFiles
where not ([FileName] is null or left([FileName],1) = ' ')

drop table #NewFiles




Edited by - MuffinMan on 04/25/2002 11:37:24
Go to Top of Page

goheat
Starting Member

4 Posts

Posted - 2002-04-25 : 17:48:22
Thnaks for the script, MuffinMan. That did the trick!

John

Go to Top of Page
   

- Advertisement -