SQL Stored Procedure using HTTP Object to Request File and Download locally

I have a database of the remote location of over 500 images that I would like to download.
(Ex. http://www.mygolfgear.com/products/iStourplusequilizer.jpg)

I am writing a stored procedure that uses an http object to download the images locally.

For the purposes of answer in the question, let's name the stored procedure sp_download_images
The file name being download is @filename
The local destination for the image is @filelocal
The remote http location for the image is @filehttp

I have written the cursor to get the image names, but I need assistance in coding the creation of the http object and invoking the object to download the file from the remote http location to the local destination.


Here is the ASP Equivalent of what I am trying to do in SQL.  I think all that needs to be done is use SP_OACreate and SP_OAMethod, but I am unsure of the method arguments for SP_OAMethods

      sSource = http://www.mygolfgear.com/products/iStourplusequilizer.jpg      sDest = C:\temp\iStourplusequilizer.jpg

      set oHTTP = Server.CreateObject("Microsoft.XMLHTTP")

      oHTTP.open "GET", sSource, False

      set oStream = server.createobject("adodb.stream")
      Const adTypeBinary = 1
      Const adSaveCreateNotExist = 1
      Const adSaveCreateOverWrite = 2
      oStream.type = adTypeBinary
      oStream.write oHTTP.responseBody
      oStream.savetofile sDest, adSaveCreateOverWrite
      set oStream = nothing
      set oHTTP = nothing

Who is Participating?
Melih SARICAOwnerCommented:
Here is an solution,

It works fine if u wanna see response text. but cant make it write responsetext into the stream. Donno why ..

u try it..

--Download HTTP by Melh SARICA
create Proc dbo._DownloadHTTP(
           @src varchar(400),
           @trg varchar(400)

declare @ohttp int
declare @ostream int
declare @ResponseBody binary
declare @result int

declare @adTypeBinary int
declare @adSaveCreateNotExist int
declare @adSaveCreateOverWrite int

set @adTypeBinary = 1
set @adSaveCreateNotExist = 1
set @adSaveCreateOverWrite = 2

set @src =N'http://www.microsoft.com'
set @trg =N'C:\temp\a.html'

exec @RESULT = SP_OACreate 'Microsoft.XMLHTTP', @ohttp out
  if @result = 0
            Print 'Http Object             : Created'
            exec @RESULT = sp_Oamethod @ohttp, 'OPEN',null, 'GET',@src,False
            if @result = 0 print 'Http Open             : Ok  Addr : ' + @src
            exec @RESULT = sp_Oamethod @ohttp, 'SEND'
            if @result = 0 print 'Http Send             : Ok'
            exec @RESULT = sp_Oamethod @ohttp,'RESPONSETEXT',@ResponseBody out
            if @result = 0 print 'Http Response             : Ok'      

            exec @RESULT = SP_OACreate 'AdoDb.Stream', @ostream out
            if @Result = 0
                        Print 'Stream Object             : Created'
                        exec @RESULT = sp_OaSetProperty @ostream, 'TYPE',@adTypebinary
                        if @result = 0 print 'Stream Type Set            : Ok  Type : ' + str(@adTypebinary,1)
                        exec @RESULT = sp_Oamethod @ostream, 'OPEN'
                        if @result = 0 print 'Stream Open             : Ok'
                        exec @RESULT = sp_Oamethod @ostream,'write',@ResponseBody
                        if @result = 0
                              print 'Stream Write             : Ok'                                          
                              print 'Stream Write             : Error'                                                                  
                        exec @RESULT = sp_Oamethod @ostream,'SaveToFile',@trg,@adSaveCreateOverWrite
                        if @result = 0
                              print 'Stream Saved to File      : Ok  File : '+      @trg
                                 print 'Stream Saved to File      :   Error : '+      str(@result,15)


            EXEC sp_OADestroy @ostream
            EXEC sp_OADestroy @ohttp

But i advice u to do it via DTS object. It would Works Fine...

How to do it wth DTS.

-- Table Creation script
Use NorthWind
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_Downloadimages]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[_Downloadimages]

CREATE TABLE [dbo].[_Downloadimages] (
      [srchttp] [varchar] (400) COLLATE SQL_Latin1_General_CP1254_CI_AS NOT NULL ,
      [trgfile] [varchar] (400) COLLATE SQL_Latin1_General_CP1254_CI_AS NOT NULL ,
      [processed] [tinyint] NOT NULL

-- DTS Script

'  Visual Basic ActiveX Script

Function Main()
dim iRs
dim mCOnn
dim mstream
dim mHttp
dim mData
set mconn = createobject("adodb.Connection")
set iRS = createobject("AdoDb.Recordset")
set mhttp =CreateObject("Microsoft.XMLHTTP")
mconn.open "Driver=SQL Server;server=localhost;Trusted COnnection=True;"
irs.open "select * from northwind.._Downloadimages where processed=0",mconn,1,3,1
set mstream = createObject("Adodb.Stream")
mstream.type= 1
while not irs.eof
  mhttp.open "GET" , irs("srcHttp"),false
  mData = mhttp.Responsebody
  mstream.write mdata
  mstream.savetofile irs("trgfile"),2
set mhttp= nothing
set mstream = nothing
set irs = nothing
set mconn = nothing

      Main = DTSTaskExecResult_Success
End Function

webagoAuthor Commented:

Coding is exactly what I was looking for.

For some reason the sp_OAMethod  ostream 'Write' is getting an error.
Can you think of a reason?

Where in the open is it setting the argument for @trg  as the file to open.
Melih SARICAOwnerCommented:
Well if i must say the truth.. I dont know.. What is teh problem.. Maybe an Adressing Problem or A Bug not sure..

For that reason i did send u an activex script. for a DTS package..

Create an Activex DTS Object. Shedule it with a job.. And then Delete Job Shcedule and Run The Job When Ever U want with an SQL Command..

Command like msdb..sp_Start_Job '<JOB Name>'

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.