Copying a file from a webserver to local disk
-
I am sure this is a simple job, but I can't seem to be able to find the right combination of search words to find a solution for an otherwise simple job.
There is an XML file on a web server out there in the wild that I'd like to copy to my local disk so that I can perform some interrogation on it. It's an XML file and for testing purposes let's assume it exists at:
http://www.mydomain.com/xml/test.xml
How can I copy this file in a stored procedure to the local disk, (c:\ for example)?
Is this possible?
Any help MUCH appreciated.
cheers
phil
-
are you sure you want to do in in SQL Server? why not a programming language, like .NET,VB6 or something? you stated you just want to copy it to disk, but that seems like just a stepping stone to fiddling witht he content, right?
here's an example of creating a web object and putting the contents of the web page into a table, which you could then manipulate.
NoCount On
Declare
@vRootURLVarchar(100),
@vFileNamevarchar(20),
@vChannelIDInt,
@oHTTPInt,
@vErrInt,
@vDLengthInt,
@vCurLengthInt,
@vStartInt,
@vEndInt,
@vFinalURLVarchar(500)
Create Table #T(Data Text)
Create Table #T2(Ident Int Not Null Identity(1, 1), Data char(4))
Set @vRootURL = 'http://localhost/'
Set @vFileName = 'guide2.htm'
Set @vFinalURL = @vRootURL + @vFileName
Exec @vErr = sp_OACreate 'Msxml2.XMLHTTP.3.0', @oHTTP OUT
Exec @vErr = sp_OAMethod @oHTTP, 'Open', NULL, 'GET', @vFinalURL, False
Exec @vErr = sp_OAMethod @oHTTP, 'Send'
Insert Into #T Exec @vErr = sp_OAMethod @oHTTP, 'responseText'--, @vResponse OUTPUT
EXEC sp_OADestroy @oHTTP
Select * From #T
Drop Table #T
Drop Table #T2
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Hi Lowell, thank you for your response.
The reason I am trying to do this in SQL Server is that I am performing a sp_xml_preparedocument, well, actually, a slightly modified version of sp_xml_preparedocument which takes a file path rather than a variable holding the XML.
Problem is the file needs to be local, it doesn't work accessing it over the internet. So I was hoping to just copy the file to the local disk and then run it from there.
However, looking at your suggestion, I may change things around. The only problem I seem to have running the above suggestion is that the XML returned in the table is truncated. I don't get the full contents of the file. Is there a size limitation on the 'responseText' call?
The XML file I am trying to use is http://www.exodus.co.uk/xml/aaa.xml.
Any ideas?
and thank you for your help !
cheers
phil
*Edit*
Actually, this is not true. Using SQL Query Analyzer, there is a setting to limit the length of returned columns, so that was why I thought it was being truncated. cheers!!
-
Hi Phil,
I use ActiveX scripts within DTS packages to download files from HTTP sites. If this is necessary as part of a procedure, I execute the package using EXEC master.dbo.xp_cmdShell 'DTSRun....'
I've created a sample script below
Function Main()
Dim objSvrHTTP
Dim fso
Dim f
Dim outFile
Dim response
outFile = "C:\aaa.xml"
Set objSvrHTTP = CreateObject("Msxml2.ServerXMLHTTP.4.0")
' Get XML data
objSvrHTTP.open "GET", "http://www.exodus.co.uk/xml/aaa.xml", false
objSvrHTTP.send
' Write to local File
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.createtextfile(outFile, true)
' Doesn't like your £ sign so replaced the unprintable char with £ sign
' can't suss why this happens but should work now
response = Replace(objSvrHTTP.responseText,"?","£")
f.Write response
f.close
Set f = Nothing
Set fso = Nothing
Main = DTSTaskExecResult_Success
End Function
Note the ? in the script, this needs to be replaced by the unprintable char. To capture the unprintable char you can use another activeX script to be executed as a single step and then deleted once captured. The script below will give you the unprintable char in the input box:
Function Main()
Dim objSvrHTTP
Set objSvrHTTP = CreateObject("Msxml2.ServerXMLHTTP.4.0")
' Get XML data
objSvrHTTP.open "GET", "http://www.exodus.co.uk/xml/aaa.xml", false
objSvrHTTP.send
inputbox "Capture this character and paste in to the ActiveX script in place of the ?", "Character Copy", Mid(objSvrHTTP.responseText, 2562, 1)
Main = DTSTaskExecResult_Success
End Function
You can then copy and paste this character from the box in to any similar script.
I don't know why it shows as a ? and even returns as ASCII code 63.
Hope this helps! If this solution is useful I can email you with a .dts file with a script already complete with special char.
-
Adrian, man, I can't thank you enough. This works brilliantly!
I've never coded an activeX script before in my life managed to get the above script into a package and with the inserted unprintable character no problems.
A quick question though, if I wanted to pass the URL through to this package rather than the hard-coded http://www..., can you tell me how I would adjust the script? In all cases I would use a static file on my local system, c:\aaa.xml and just over-write this each time.
Again, thanks so much for this, I've been spending hours trying to find a solution and this works straight out the box - cheers!
-
You would have to pass the web address as a global variable, named gv_WebAddress for example, and change the line:
objSvrHTTP.open "GET", "http://www.exodus.co.uk/xml/aaa.xml", false
TO
objSvrHTTP.open "GET", DTSGlobalVariables("gv_WebAddress").Value , false
This should do the trick.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply