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. — Pondlife
– Pondlife, Commented Feb 15, 2013 at 20:06
Yes, I saw these questions, but openrowset doesn't work with http. — Roman Badiornyi
– Roman Badiornyi, Commented Feb 15, 2013 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
– Pondlife, Commented Feb 15, 2013 at 22:46

Mark Whitfeld · Accepted Answer · 2014-05-08 08:14:42Z

5

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 :)

answered May 8, 2014 at 8:14

Mark Whitfeld

6,9035 gold badges39 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jim31415 Over a year ago

This works for small images, but returns NULL for files > 8K. Any way of getting larger images?

Collectives™ on Stack Overflow

Read binary data in TSQL through HTTP GET

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related