We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

on
Medium Priority
617 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
Comment
Watch Question

## View Solution Only

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

CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
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 :



Not the solution you were looking for? Getting a personalized solution is easy.

Commented:
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!
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
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.

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

Thanks again!
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Pleasure, glad to see it working...
##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile