Read binary data in TSQL through HTTP GET

Question

I need to export some images into my SQL Server through HTTP URL.

I've found article about exporting of XML data:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Declare @Object as Int; 
Declare @ResponseText as Varchar(8000); 
Declare @Url as Varchar(MAX); 
select @Url = 'http://somexml.com/xmlfile.xml'

Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT; 
Exec sp_OAMethod @Object, 'open', NULL, 'get', @Url, 'false' 
Exec sp_OAMethod @Object, 'send' 
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT      
Exec sp_OADestroy @Object 

--load into Xml 
Declare @XmlResponse as xml; 
select @ResponseText

Also during research I've found that I should use ADODB.Stream for binary data. But I can't figure out how to read this object using approach described above. Is there a way for reading binary data in pure TSQL or I should use CLR for this?

Thanks a lot for helping.

It would be easier to write a .NET program to download the files and then load them into the database. You can run it as a service or schedule it with SQL Agent if you want. TSQL is usually not a good language for working with anything outside the database, although loading binary files is possible and has been asked about many times, e.g. [this question](http://stackoverflow.com/questions/3925656/importing-images-in-sql-server-2005). — Pondlife, Feb 15 '13 at 20:06
Yes, I saw these questions, but openrowset doesn't work with http. — Roman Badiornyi, Feb 15 '13 at 22:39
Correct, and the main point of my comment was that you shouldn't do this in TSQL anyway, you should do it in an external program. Not everything can or should be done inside the database: TSQL can load binary files from a file system but it has no support for HTTP or other protocols. If you absolutely must do it in the database then you can write a SQLCLR procedure, but I wouldn't recommend it. — Pondlife, Feb 15 '13 at 22:46

score 5 · Answer 1 · answered May 08 '14 at 08:14

I had the same question as you, and found how to do it! Here is my sql to do it (it supports retrieval of text and binary data):

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

declare @xmlObject as int
declare @responseText as varchar(max)
declare @responseBody as varbinary(max)
declare @status as int
declare @url as varchar(2048)

select @url = 'http://someurl.com/someimage.jpg'

exec sp_OACreate 'MSXML2.XMLHTTP', @xmlObject OUT;
exec sp_OAMethod @xmlObject, 'open', NULL, 'get', @url, 'false'
exec sp_OAMethod @xmlObject, 'send'
exec sp_OAMethod @xmlObject, 'status', @status OUTPUT
exec sp_OAMethod @xmlObject, 'responsetext', @responseText OUTPUT
declare @responseTable as table ( body varbinary(max) )
INSERT INTO @responseTable exec sp_OAMethod @xmlObject, 'responsebody'
exec sp_OADestroy @xmlObject

select @status
select @responseText
select @responseBody=body from @responseTable    
select @responseBody

Hope this helps somone out there :)

This works for small images, but returns NULL for files > 8K. Any way of getting larger images? — jim31415, Jun 05 '19 at 19:35

Read binary data in TSQL through HTTP GET

1 Answers1