• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 491
  • Last Modified:

Using T-SQL to download file from a webserver

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
hennessym
Asked:
hennessym
  • 3
  • 2
1 Solution
 
reb73Commented:
If you have SQL Integration Services installed, you could use SSIS to automate the download, see following link for tips -

http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/2b7cf6b5-d16d-44cd-954d-68e9ba98ee91/
0
 
Mark WillsTopic AdvisorCommented:
Or, you can simply run a java script which you could create on the fly, or add in arguments to an existing script....




if object_id('js_mydownload_script','U') is not null drop table js_mydownload_script
create table js_mydownload_script (line varchar(255))
 
insert js_mydownload_script values ('var fso = new ActiveXObject(''Scripting.FileSystemObject'');')
insert js_mydownload_script values ('var xmlhttp=new ActiveXObject("microsoft.xmlhttp");')
insert js_mydownload_script values ('xmlhttp.open("GET", "http://torstatus.kgprog.com/query_export.php/Tor_query_export.csv", false);')
insert js_mydownload_script values ('xmlhttp.send();')
insert js_mydownload_script values ('var data=xmlhttp.responsetext;')
insert js_mydownload_script values ('var file = fso.CreateTextFile("c:\\ee\\Tor_query_export.csv",true);')
insert js_mydownload_script values ('file.writeLine(data);')
insert js_mydownload_script values ('file.close();')
 
 
exec xp_cmdshell 'bcp "select * from mydbname..js_mydownload_script" queryout "c:\ee\js_my_download_script.js" -c -T -CACP'
 
exec xp_cmdshell 'cscript.exe c:\ee\js_my_download_script.js'
 
 
 
-- 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 :
 
exec xp_cmdshell 'cscript c:\ee\js_download_script.js http://torstatus.kgprog.com/query_export.php/Tor_query_export.csv c:\ee\Tor_query_export.csv'

Open in new window

0
 
hennessymAuthor 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.

Here's my js_my_download_script.js (I'm seeing a "path not found" error):

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Mark WillsTopic AdvisorCommented:
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
 
hennessymAuthor Commented:
Ahh, that's it - I needed two extra \\ before myshare.

Thanks again!
0
 
Mark WillsTopic AdvisorCommented:
Pleasure, glad to see it working...
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now