I have a few remote SQL servers that I need to pull a large amount of data from regularly (say 5,000,000 rows per server). This data also needs to be formatted and FTPed to another server. The dial-up portion is fine, I can connect and communicate with the server, but sometimes the connection is slow, maybe only 19Kbps.
Once connected I tried to use System.Data.SqlClient.SqlBulkCopy, but it just throws a timeout error. I've set the timeout on the bulk copy to 7200 seconds, and the timeout on the connection strings to each database at 900 seconds, but it still times out at around 30 seconds.
I tried using a DataReader and running SqlCommand.ExecuteNonQuery() to insert each row, which works OK, but it's slow and sometimes the connection is lost.
I also tried setting up a DTS package on the remote servers, scheduling them to dump the data I need to text, and then tried downloading the files. But, they can be a few hundred MB (possibly even GB on some servers) and the dial-up connection is usually dropped at some point.
What I ended up doing was creating a small app in C (a few of these are WINNT and that was the easiest way, it also allows other to retrieve the data manually if necessary without the ability to alter the source) that takes a few arguments to build the query I need. It then runs the query and dumps the results in the required CSV format. Then it calls 7zip with the highest compression level to compact the data as small as possible (this reduces a 500MB file to about 20MB).
Because I have to bring the data back to me before I can FTP it to the necessary place, and the remote servers don't have any internet access, I'm still just copying the file to a windows share, then decompressing it locally and FTP the uncompressed data (as requested) to it's destination.
This may not be the best approach, but it's working. Thanks