Solved

Posted on 2009-02-18
470 Views
I'm looking to automate the download of a file from a webserver in SQL Server 2005.

From what I've read it was possible in SQL 2000 to download a file from an FTP site, so I'm sure SQL 2005 has similar capability.

Does anyone know, however, if it's possible to automate the download of a file from a plain-old webserver?  If it helps, this is the file I need: http://torstatus.kgprog.com/query_export.php/Tor_query_EXPORT.csv
0
Question by:hennessym

LVL 25

Expert Comment

If you have SQL Integration Services installed, you could use SSIS to automate the download, see following link for tips -

0

LVL 51

Accepted Solution

Or, you can simply run a java script which you could create on the fly, or add in arguments to an existing script....



-- or create a java script file - just a text file edited in notepad (from the first var down to the close)

/* this script was found on the web, and if I find the person again, will include their name...

var fso = new ActiveXObject('Scripting.FileSystemObject');

var args = WScript.Arguments;

var url = args(0);

var fileName = args(1);

var xmlhttp=new ActiveXObject("microsoft.xmlhttp");

xmlhttp.open("GET", url, false);

xmlhttp.send();

var data=xmlhttp.responsetext;

var file = fso.CreateTextFile(fileName, 2);

file.writeLine(data);

file.close();

*/

-- then your sql becomes even easier :


0

LVL 1

Author Comment

Thanks, guys!

Reb, that appears to be a script to download a file from an FTP server, whereas I need to download a file via a webserver.

Mark_willis, your script works great for saving the downloaded file to a local drive, but I need to save it to a network share - is there a trick for doing that?  I've tried using both the drive letter and the UNC path to no avail.

var fso = new ActiveXObject('Scripting.FileSystemObject');
var xmlhttp=new ActiveXObject("microsoft.xmlhttp");
xmlhttp.open("GET", "http://torstatus.kgprog.com/query_export.php/Tor_query_export.csv", false);
xmlhttp.send();
var data=xmlhttp.responsetext;

dt = new Date();
month = dt.getMonth() +1
date = dt.getDate()
year = dt.getFullYear()

var fileloc = "\\myshare\\myfolder\\Tor IPs" + "Tor_query_export" + "-" + month + "-" + date + "-" + year
var file = fso.CreateTextFile(fileloc,true);
file.writeLine(data);
file.close();

Thanks again!
0

LVL 51

Expert Comment

Is that network share known to the SQL Server user - ie the one who starts it up as a server ?

Also the backslash is an escape character in JS, so you need an extra one and I don't think there is enough in your fileloc variable to begin with...

You can play in a command window by simply running the cscript command until it is working OK.
0

LVL 1

Author Comment

Ahh, that's it - I needed two extra \\ before myshare.

Thanks again!
0

LVL 51

Expert Comment

Pleasure, glad to see it working...
0

## Featured Post

### Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…