Performing an HTTPGET from SQL Server 2005

Question

Performing an HTTPGET from SQL Server 2005

samaspin

SSC Veteran

Points: 229
More actions
August 4, 2008 at 10:09 am

#196491

Hi Folks,
I'm trying to make an HTTPGET from within SQL as it would make my life a whole lot simpler!!
I've tried a couple of similar examples but receive error messages at different steps of the sprocs.
Here's one example I've tried...
********************************************************
alter procedure test_http_get( @sUrl varchar(200), @response varchar(8000) out)
As
Declare
@obj int
, @hr int
, @status int
, @msg varchar(255)
exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
if @hr < 0 begin Raiserror('sp_OACreate MSXML2.ServerXMLHttp failed',
16,
1) return end
exec @hr = sp_OAMethod @obj, 'Open', NULL, 'GET', @sUrl, false
if @hr <0 begin set @msg = 'sp_OAMethod Open failed' goto eh end
exec @hr = sp_OAMethod @obj, 'send'
if @hr <0 begin set @msg = 'sp_OAMethod Send failed' goto eh end
exec @hr = sp_OAGetProperty @obj, 'status', @status OUT
if @hr <0 begin set @msg = 'sp_OAMethod read status failed' goto eh end
if @status <> 200 begin set @msg = 'sp_OAMethod http status ' +
str(@status) goto eh end
exec @hr = sp_OAGetProperty @obj, 'responseText', @response OUT
if @hr <0 begin set @msg = 'sp_OAMethod read response failed' goto eh
end
exec @hr = sp_OADestroy @obj
return
eh:
exec @hr = sp_OADestroy @obj
Raiserror(@msg, 16, 1)
return
go
********************************************************
this one fails with the error "sp_OAMethod Send failed"
If anybody can give me some pointers about what i can do that would be great!
Cheers
Sam

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

I tried this and added acouple of different things, like timeouts and Content-Length to the headers...it made no difference...depending on the url it either failed witha 404, or the same error as you got.

a different thread with two different solutions: both avoid SP_OACREATE, and use either a DTS/SSIS task, or a separate dll to do the work;

hope that helps

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

samaspin SSC Veteran Points: 229 More actions · Answer 2

Yeh, I think avoiding SP_OACREATE might be the key here!! I'm currently working on doing this via a CLR UDF.... just have some security issues to get round first. If that fails I'll have a look at the DTS or SSIS route

Thanks for the advice : )