I have an app that updates one or more SQL Server 2000 databases from
data contained in one or more text files and also ftps some wav files.
At the moment this process is triggered by a button press in a Windows
app and I run it every day.
The dbs are on one machine, the text files on another and the wav files
are on a third. All three run Windows Server 2003 and are within the
same network but the SQL Server machine is in a different physical
location. Currently I run the app on my workstation (which is in the
same physical location as the the latter two machines) and update
copies of the dbs on my local instance of SQL Server, which I then DTS
to the main SQL machine. I'm doing this because the connection to the
main SQL machine is slow and the update process takes ten-twenty times
longer over the network.
What's the best strategy for setting this up to run efficiently and at
a specified time each day? Obviously, I'd like the process to update
the main dbs directly and dispense with the DTS. I guess I need to do
something like set up a Windows Service on the SQL machine that fires
at a given time (how do I make it do that?) and calls a method on
another Service on the text files machine to read the data and return a
Dataset. (I've never written a Windows Service before, so they're a bit
of a mystery...)
Any help much appreciated.
