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

Using T-SQL to download file from a webserver

hennessym
hennessym asked
on
Medium Priority
617 Views
Last Modified: 2012-06-21
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

Commented:
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/
Topic Advisor, Page Editor
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....




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

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

Ask the Experts

Author

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!
Mark WillsTopic Advisor, Page Editor
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.

Author

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

Thanks again!
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Pleasure, glad to see it working...
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

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

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.