hoekstra.co.uk

The need for dirty little Windows hacks that can only be done in crude DOS batch scripts still crops up on a regular basis for system integrators, who sometimes have to ignore their moral compunctions and resort to FTP'ing between legacy systems. Here's one that vexes many hardened hackers: What is the simplest and cleanest way in Windows to send a file to an FTP server using a batch script?

This example batch script creates a data extract from a SQL Server database into the file myfile.csv, and then FTP's it to somewhere interesting. Create a text file called examplefileftp.cmd with the following in it, and replace the strings mydatabase, myftpserver, myuserid and mypassword with real-world values:

@echo off
: Collect data from a database in CSV file...
echo Removing file if it already exists...
del myfile.csv
echo Done....
echo Extracting data...
osql -E -d mydatabase -Q "select stuff from sometable" -s "," -o myfile.csv
echo Done....
: Send file to FTP server
echo Sending extracted data...
@ftp -i -s:"%~f0"&GOTO:EOF
open myftpserver
myuserid
mypassword
put myfile.csv
disconnect
bye...

Ignore all error warnings that FTP creates. And don't ask what this line does:

@ftp -i -s:"%~f0"&GOTO:EOF

But it works!