| Author |
Topic |
|
lane0618
Posting Yak Master
134 Posts |
 Posted - 2004-04-07 : 13:55:04
|
| I have a table with a single column that has a record for each file in a certain directory. Is there a way to "sync" the table so that it automatically updates whenever a file is added or deleted from the direcory?I have a DTS package that does this every 30 minutes, but I'm hoping there is a better way.thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-07 : 14:01:00
|
| What causes a file to be added or deleted from a directory though? Is it done manually or through an application?Tara |
 |
|
|
lane0618
Posting Yak Master
134 Posts |
Posted - 2004-04-07 : 14:02:29
|
| Its a manual process. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-07 : 14:07:33
|
| The only way that I can think of is to write a service. The service would wake up every once in a while that would check for changes. The service would then update the database if needed. Instead of DTS, you could use a stored procedure that is scheduled to run on a frequent basis. The stored procedure would use xp_cmdshell with cmd commands. What does the table look like?Tara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-07 : 14:15:06
|
| Set up a job to do it. Have it execute a stored procedure that runs xp_cmdshell 'dir c:\directory' into a table. Compare the results and do what you need to do. You can schedule the job to run every 15 minutes or so.Is this what you're looking for?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
lane0618
Posting Yak Master
134 Posts |
Posted - 2004-04-07 : 14:15:17
|
| So there is no way to detect activity for a directory, and use that to start the stored proc or DTS? I would really like to make this truly synced if at all possible.table looks like this:pdfList======== 5000127.pdf 5000141.pdf 5000142.pdf 5000149.pdf 5000152.pdf 5001002.pdf 5001028.pdf |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-07 : 14:18:56
|
| You would have to an application check for activity. SQL Server can't do it on its own. SQL Server can run jobs and it would appear to be automatic if the job runs often enough.Tara |
|
|
|
lane0618
Posting Yak Master
134 Posts |
Posted - 2004-04-07 : 16:33:29
|
| Ok, I found a program that will automatically run a batch file when a file in the directory is added/deleted:http://downloads-zdnet.com.com/3000-2248-10196647.html?tag=lst-0-10I know I can use xp_cmdshell in the batch to execute a stored procedure, but can I put the contents of a direcotry (the list of files) directly into a #temp table (without having to first use something like "dir /b > fileList.txt" and then importing the textfile)?Thanks! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-07 : 16:36:14
|
| INSERT INTO #TempEXEC master.dbo.xp_cmdshell 'dir /B > filelist.txt'So it is done in one statement.Tara |
|
|
|
|
table/directory sync