March 13, 2003 at 11:46 pm
I would like to set up a database in such a way that a database import/insert is automatically triggered when one ore more comma-separated ASCII-files (ore EDI-files) are added to a directory (should work with Win NT 4 and W2K). Does anyone have any information on how to do this? Is it possible to do it just with SQL Server, or does one need to involve the OS and/or a third-party software?
March 14, 2003 at 3:38 am
You could setup SQL to run a job every minute to check if you want to stay strict SQL. Or you could write a small app with a thread that monitors the Windows message pump looking for a change in that path/folder.
March 14, 2003 at 8:35 am
The ReadDirectoryChanges API lets you know of changes. This API is a beast in VB
to implement as it requires multi threading. Their are 3rd party controls that
let you listen to these events from VB out there. Just google this API, and you
should find a couple.
Or you can use WMI from VB to event off of the folder when a file gets created.
I can post a sample script or you can find one on MSDN at :
http://www.microsoft.com/hwdev/driver/wmi/default.asp All their
examples are in VBScript, but can be easily converted to VB. Oh, you can even
event with WMI from a remote machine with appropriate rights.
Out of the 2 scenarios :
a) polling from sql server
b) eventing off the directory
I highly recommend eventing(b) over polling(a).
Tim C.
//Will write code for food
Tim C //Will code for food
March 15, 2003 at 5:38 am
You can do the same thing using .Net with a lot less headache (uses the same API I imagine). One thing you have to watch for is it will fire when the file gets created - which doesn't always happen in a second. With a large file it could take a while to finish writing, but your process would get triggered immediately. Usual approach is to look for a second "flag" file that is usually a zero byte file with a similar name, your process looks for anything ending in '.done' - when it finds one it knows that a file with the same prefix is now available or the .done file actually contains the name or names of files to process.
Andy
March 19, 2003 at 12:01 am
This script will let you know when files are created It is a straight copy and paste from the Windows 2000 Scripting Guide Book, and I have tested it out :
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colMonitoredEvents = objWMIService.ExecNotificationQuery _
("SELECT * FROM __InstanceCreationEvent WITHIN 10 WHERE " _
& "Targetinstance ISA 'CIM_DirectoryContainsFile' AND " _
& "TargetInstance.GroupComponent= " _
& "'Win32_Directory.Name=""d:\\\\temp""'")
Do
Set objLatestEvent = colMonitoredEvents.NextEvent
Wscript.Echo objLatestEvent.TargetInstance.PartComponent
Loop
I do agree with Andy however that .Net is the cleaner solution, but not everyone has .Net yet...
Tim C.
//Will write code for food
Tim C //Will code for food
March 21, 2003 at 8:36 am
Can we do the same (as above) with Windows Shell Scripting?! Because I do administer all my Servers Remotely and VB is not installed on the servers.
I do not have any idea with Shell Scripting as of now.
Regards,
Murali Damera.
.
March 21, 2003 at 8:59 am
The above code is VBScript. It is designed to run in WSH, so just copy it into a VBscript file, and change the path ensuring you keep the \\\\ four slashes between the folder paths. run it via "cscript.exe /nologo YourFileNameHere.vbs", and then create a file in that directory and you will see that it outputs the filename to StdOut. Whats even better, is that you can run this remotely against a remote server to get events from its directory(assuming person executing the script has rights). To do so change the strComputer = "." to strComputer = "ComputerName". Enjoy...
Tim C.
//Will write code for food
Tim C //Will code for food
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply