0

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.

Bryan Williams
  • 452
  • 1
  • 5
  • 17
  • I don't know how to get this working, I only comment that this is a strange thing to do from SQLL Server. Are you sure you need to do this from within SQL Server? – Nick.Mc Jan 09 '21 at 09:08
  • @Nick.McDermaid yes it is a strange thing. The purpose is a direct replacement for an existing sp. The problem is the old sp retrieved the file from a table, but the file now has to be downloaded from an API. I still haven't figured out how to do this. – Bryan Williams Jan 13 '21 at 15:54
  • 1
    A PowerShell script executed from SQL may be the best way to do this, but I was hoping to avoid PS. – Bryan Williams Jan 13 '21 at 15:57
  • I definitely recommend PS. Why do you want to avoid it? – Nick.Mc Jan 13 '21 at 21:46
  • 1
    I guess something in SQL calls this with a `@URL` value. I'm not across your architecture but this kind of design is really a dead end, you will need to modernise it eventually – Nick.Mc Jan 14 '21 at 08:46
  • This? https://www.mssqltips.com/sqlservertip/4963/simple-image-import-and-export-using-tsql-for-sql-server/ – wp78de Jan 04 '23 at 01:08

2 Answers2

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. :(

Jason K
  • 11
  • 1
  • 1
    If you think that this is not an answer then please wait for your commenting privilege for giving your 2 cents; because https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead If you think that this might be an answer ( see [answer] ) then please [edit] accordingly. – Yunnosch Jan 04 '23 at 07:55
0

Try this:

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

insert into @responseBody([responseBody])
   exec sp_OAGetProperty @obj, 'responseBody', @responseBody out
Suhaib Janjua
  • 3,538
  • 16
  • 59
  • 73
Philip Andrews
  • 1