How to download a binary file in T-SQL from a URL

Question

I am trying to create a SQL Server stored procedure to download an unknown binary file type (usually either jpg, pdf, zip, rtf, or doc) from the specified URL.

When this is executed, I have verified that the URL is getting invoked and the file is downloading correctly (the same URL is also used by other services), but the script is not grabbing the file contents.

create procedure DownloadFile (@Url varchar(1024))
AS
BEGIN
    SET NOCOUNT ON;

    declare @obj int
    declare @responseBody varchar(8000)
    declare @status varchar(50)
    declare @statusText varchar(1024)
    
    exec sp_OACreate 'Msxml2.XMLHTTP', @obj out
    exec sp_OAMethod @obj, 'Open', null, 'GET', @Url, false
    exec sp_OAMethod @obj, 'send'
    exec sp_OAGetProperty @obj, 'responseBody', @responseBody out   
    exec sp_OAGetProperty @obj, 'status', @status out
    exec sp_OAGetProperty @obj, 'statusText', @statusText out
    exec sp_OADestroy @obj  
    
    select @responseBody as responseBody,datalength(@responseBody) as responseBodyDataLength, @status as [status], @statusText as [statusText]

END 

Obviously the @responseBody type needs to be some type of binary type, but when I use any of the binary types such as binary, varbinary, or image, @responseBody is always NULL.

When I declare @responseBody as a varchar(8000), it ends up with this strange 9-char-long string: '?????AA??'. But if I declare @responseBody as a varchar(max), it ends up as NULL.

I'm very confused.

The sp needs to return the file contents to the caller, once I get this working.

Answer 1

My 2 cents (not an answer): sp_OAGetProperty 'ResponseBody' is incapable of returning data (maybe only binary format issue) when that data is more than 8K. I have struggled to find a way around this, but I always end up back at the same place.

sp_OAGetProperty works fine to get an image as a varbinary if the image is less than 8K. :(

Answer 2

Try this:

declare @responseBody table([responseBody] varbinary(max))

insert into @responseBody([responseBody])
   exec sp_OAGetProperty @obj, 'responseBody', @responseBody out