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 FileNamesINSERT 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 |
|
|
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. |
|
|
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) |
|
|
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 |
|
|
goheat
Starting Member
4 Posts |
Posted - 2002-04-25 : 10:44:53
|
FOLLOW-UPThis 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! |
|
|
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. |
|
|
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 oncreate 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 namesselect @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 FileNamesselect [FileName] = RTrim(SubString([FileName], 40, 255)) , [Size] = Convert(BigInt, Replace(SubString([FileName], 20, 20), ',', '')) , Modified = convert(datetime, Replace(left([FileName], 18), ' ', ' ') + 'm')from #NewFileswhere not ([FileName] is null or left([FileName],1) = ' ')drop table #NewFiles Edited by - MuffinMan on 04/25/2002 11:37:24 |
|
|
goheat
Starting Member
4 Posts |
Posted - 2002-04-25 : 17:48:22
|
Thnaks for the script, MuffinMan. That did the trick! John |
|
|
|