How to make HTTP call out of SQLServer 2000?

Question

How to make HTTP call out of SQLServer 2000?

periakaruppan

SSC Enthusiast

Points: 118
More actions
January 13, 2005 at 2:53 am

#163418

Hi,
Thru SQLServer's stor. proc., need to submit some data to an URL, get the returned data and do something based on that. How can i do that in SQL 2000 server? Any extended stor. proc.? Thanks in advance for the help.
Regards,
Peri

Viewing 9 posts - 1 through 8 (of 8 total)

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

Andy Butson SSC Veteran Points: 248 More actions · Answer 1

Hey

Your project sounds interesting unfortunately there isn’t any pre-rolled functionality. I do have one quick question. Have you ever programmed c, c++ or even vb? And have you ever tried experimenting with winsock?

The easiest way to achieve this via c/c++ is to wrap your own xp pretty straight forward (I will help if you want). But if you don’t understand the code don’t release it!!!

If a homemade xp isn’t practical them it’s possible to write a COM dll in vb and call it via SQL. First of all write a stateless function to do what ever you need in vb. Then just call the object from SQL. You well need the vb runtime on the server L

If you want more information on either option I’m more than happen to go into detail.

Cheers

Andy

hendrik@kreax.com Valued Member Points: 62 More actions · Answer 2

Hello,

something similar to this has been asked before somewhere ... One option is to use ServerXML.HTTP from SQL Server ... We do this in an ActiveX task from DTS, but I am sure it is possible to achieve this with sp_OACreate also.

Good luck!

Hendrik

Bert De Haes Hall of Fame Points: 3078 More actions · Answer 3

I recently wrote a stored procedure I use to post data to an URL and retrieve the result. The procedure posts data as is done by a html form with action=POST.

Here is the script :

/*

This is a procedure, not an udf because of the exec insert at the end

The exec insert is needed because the response can exceed 8000 characters

Normally, one should call methods like this :

exec @rc = master.dbo.sp_OAMethod @http, 'Open', NULL, 'POST' ,'www.d-trix.com/default.asp', 0

But this does not work with WinHttp.WinHttpRequest.5.1 and gives different errors

So we call methods like this

exec @rc = master.dbo.sp_OAMethod @http, 'Open("POST","http://www.d-trix.com/default.asp",0)'

if a (temp) table with the name #usp_httppost exists, the procedure will

insert the result into that table.

if not, the procedure will return a resultset with the results

*/

create procedure dbo.usp_httppost

(

@URL varchar(512),

@post varchar(4000),

@WebLogin varchar(128) = null,

@WebPassword varchar(128) = null,

@ProxyLogin varchar(128) = null,

@ProxyPassword varchar(128) = null

)

/*

returns @usp_httppost table

(

return_status bit null, -- 0 is OK, 1 is error

error_msg varchar(4096) null,

HTTP_Status varchar(30) null,

HTTP_AllResponseHeaders text null,

HTTP_ResponseText text null

&nbsp

*/

as

begin

/*******************************************************************

Name : usp_httppost

Server : SQLserver 2000

Description : Post data as coming from an HTML FORM with METHOD=POST

to an URL and retrieve the result.

Parameters : @URL : the url to use ( like https://www.d-trix.com )

@post : the parameters to post

@WebLogin : (optional) The Username for the webserver

@WebPassword : (optional) The password for the webserver

@ProxyLogin : (optional) The Username for the proxyserver

@ProxyPassword : (optional) The password for the proxyserver

Notes : . The data to be posted should be like param1=val1&par2=val2

. if a table called #usp_httppost exists, the result is stored

into that table. If not, the procedure returns a resultset.

Date : 2005-01-19

Author : Bert De Haes ( bertdehaes@scarlet.be )

History :

*******************************************************************/

set nocount on

declare @http int, -- the objecttoken for WinHttp.WinHttpRequest.5.1

@rc int, -- the return code from sp_OA procedures

@src varchar(255), -- the source of an error

@desc varchar(512), -- the desciption of an error

@Doing varchar(512), -- What are we doing when calling a sp_OA proc

@TableExisted bit -- Did the temp table exists yes(1) or no(0)

-- init

set @rc = 0

if object_id('tempdb..#usp_httppost') is null

begin

-- The temp table #usp_httppost does not exists

set @TableExisted = 0

create table #usp_httppost

(

return_status bit null, -- 0 is OK, 1 is error

error_msg varchar(4096) null,

HTTP_Status varchar(30) null,

HTTP_AllResponseHeaders text null,

HTTP_ResponseText text null

&nbsp

end

else

begin

set @TableExisted = 1

truncate table #usp_httppost

end

-- Insert a default record

insert #usp_httppost ( return_status, error_msg )

values ( 1 , 'Unknown error' )

-- create a table to store output from different sp_OA calls

create table #tempresult

(

HTTP_Status varchar(30) null,

HTTP_AllResponseHeaders text null,

HTTP_ResponseText text null

&nbsp

-- create the 'WinHttp.WinHttpRequest.5.1' object

set @Doing = 'Create WinHttp.WinHttpRequest.5.1 object.'

exec @rc = master.dbo.sp_OACreate 'WinHttp.WinHttpRequest.5.1', @http output

if @rc <> 0 goto Error

-- open the url on the server

set @Doing = 'Open("POST" , "' + @URL + '", 0 )'

exec @rc = master.dbo.sp_OAMethod @http, @Doing

if @rc <> 0 goto Error

-- set the SetRequestHeader

set @Doing = 'SetRequestHeader("Content-Type","application/x-www-form-urlencoded")'

exec @rc = master.dbo.sp_OAMethod @http,@Doing

if @rc <> 0 goto Error

if @WebLogin is not null AND @WebPassword is not null

begin

-- Set the Credentials for the Webserver

set @Doing = 'SetCredentials("' + @WebLogin + '","' + @WebPassword + '",0)'

exec @rc = master.dbo.sp_OAMethod @http, @Doing

if @rc <> 0 goto Error

end

if @ProxyLogin is not null AND @ProxyPassword is not null

begin

-- Set the Credentials for the Proxy

set @Doing = 'SetCredentials("' + @ProxyLogin + '","' + @ProxyPassword + '",1)'

exec @rc = master.dbo.sp_OAMethod @http, @Doing

if @rc <> 0 goto Error

end

-- send the info

set @Doing = 'Send("' + @post + '")'

exec @rc = master.dbo.sp_OAMethod @http,@Doing

if @rc <> 0 goto Error

-- Get the HTTP_Status

set @Doing = 'Status'

truncate table #tempresult

insert #tempresult (HTTP_Status)

exec @rc = sp_OAMethod @http,@Doing

if @rc <> 0 goto Error

update #usp_httppost

set HTTP_Status = #tempresult.HTTP_Status

from #tempresult

where #tempresult.HTTP_Status is not null

-- Get the ResponseHeaders

set @Doing = 'GetAllResponseHeaders'

truncate table #tempresult

insert #tempresult (HTTP_AllResponseHeaders)

exec @rc = sp_OAMethod @http,@Doing

if @rc <> 0 goto Error

update #usp_httppost

set HTTP_AllResponseHeaders = #tempresult.HTTP_AllResponseHeaders

from #tempresult

where #tempresult.HTTP_AllResponseHeaders is not null

-- retrieve the ResponseText

set @Doing = 'ResponseText'

truncate table #tempresult

insert #tempresult (HTTP_ResponseText)

exec @rc = sp_OAMethod @http,@Doing

if @rc <> 0 goto Error

update #usp_httppost

set HTTP_ResponseText = #tempresult.HTTP_ResponseText

from #tempresult

where #tempresult.HTTP_ResponseText is not null

-- Everything went well

update #usp_httppost

set return_status = 0,error_msg = 'DONE'

-- if we get here the normal way, don't do error

GOTO Cleanup

Error:

-- Get error information

if @http is not null

begin

exec sp_OAGetErrorInfo @http, @src OUT, @desc OUT

end

else

begin

set @src = '?'

set @desc = '?'

end

update #usp_httppost

set return_status = 1,

error_msg =

'Error [' + ISNULL( master.dbo.fn_hexadecimal(@rc) ,'' ) +

'], While [' + ISNULL( @Doing , '' ) +

'], Source [' + ISNULL( @src , '' ) +

'], Description [' + ISNULL( @desc , '' ) + ']'

-- Destroy created object(s)

Cleanup:

if @http is not null

begin

exec @rc = master.dbo.sp_OADestroy @http

set @http = null

if @rc <> 0 goto Error

end

-- Give the result back to the caller

Result:

if @TableExisted = 0

select * from #usp_httppost

return 0

end

go

periakaruppan SSC Enthusiast Points: 118 More actions · Answer 4

Dear All,

Thanks for the help. I will go thru the suggested procedures and will get back to you all if there is any hiccup.

Thanks and regards,

Peri

noel cacnio Newbie Points: 9 More actions · Answer 5

That was an awesome stored procedure Journeyman!

Worked well here! Thanks!

Bert De Haes Hall of Fame Points: 3078 More actions · Answer 6

Thanks ... just found a little problem. The parameter @Doing is declared as @Doing varchar(512). It should be @Doing varchar(4000), as it is used to contain the posted data ...

Bert

Eugene Liu-143102 SSC Rookie Points: 46 More actions · Answer 7

I did use the SP to do following:

exec dbo.usp_httppost 'http://froogle.google.com/froogle','q=nokia&btnG=Search+Froogle'

and got no results. But the original URL resturns data. The original URL is:

http://froogle.google.com/froogle?q=nokia&btnG=Search+Froogle

Here is what I got:

return_status,error_msg,HTTP_Status,HTTP_AllResponseHeaders,HTTP_ResponseText

0,DONE,200,Set-Cookie: PREF=ID=1b28c916df48a269:TM=1153416500:LM=1153416500:S=LXwmzermeNkwWDpt; expires=Sun, 17-Jan-2038 19:14:07 GMT; path=/; domain=.google.com

Content-Type: text/html; charset=ISO-8859-1

Server: cffe

Cache-Control: private, x-gzip-ok=""

Transfer-Encoding: chunked

Date: Thu, 20 Jul 2006 17:28:20 GMT

,

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"

"http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<title>Froogle</title>

#q{width:22.5em}

</style>

</head>

<tr>

<a href="My'>http://froogle.google.com/shoppinglist">My Shopping List</a>

</td>

</tr>

<tr>

<td align="center" valign="bottom"><a href="/"><img src=

/froogle/intl/en_us/images/froogle_110tall.gif

alt="Froogle" border="0" height="110" width="276"></a></td>

</tr>

</table>

<script><!--

// -->

</script><table border=0 cellspacing=0 cellpadding=4><tr><td nowrap><a id=0a class=q href="/webhp?hl=en&tab=fw&ned=us" onClick="return qs(this);">Web</a>    <a id=1a class=q href="/imghp?hl=en&tab=fi&ned=us" onClick="return qs(this);">Images</a>    <a id=2a class=q href="http://groups.google.com/grphp?hl=en&tab=fg&ned=us" onClick="return qs(this);">Groups</a>    <a id=4a class=q href="http://news.google.com/nwshp?hl=en&tab=fn&ned=us" onClick="return qs(this);">News</a>    Froogle    <a id=7a class=q href="/maphp?hl=en&tab=fl&ned=us" onClick="return qs(this);">Maps</a>    <a href="http://www.google.com/intl/en/options/" class=q>more »</a></td></tr></table>

<tr>

<td>

<td>

<a

href="/froogle_advanced_search?lmode=online">Advanced Froogle Search</a>

<a href="Preferences http://www.google.com/preferences">Preferences</a>

<a href=/froogle/intl/en_us/about.html>Froogle Help</a>

<td>

<label>

Remember this location

</label>

</table>

</form>

<h2 style="font-size:100%;color:#60a63a;font-weight:bold;margin:1em">froo·gle (fru'gal) n. Smart shopping through Google.</h2>

<tr><td style="border-bottom:1px solid #80c65a" colspan=5>A few of the items recently found with Froogle:<tr>

<tr>

<td width=20 nowrap><a href="/froogle?sampleq=1&q=wedding+bands">wedding bands</a></td>

<td width=20 nowrap><a href="/froogle?sampleq=1&q=glass+vase">glass vase</a></td>

<td width=20 nowrap><a href="/froogle?sampleq=1&q=poker+chips">poker chips</a></td>

<td width=20 nowrap><a href="/froogle?sampleq=1&q=rogaine">rogaine</a></td>

<td width=20 nowrap><a href="/froogle?sampleq=1&q=pasta+machine">pasta machine</a></td>

<tr>

<td width=20 nowrap><a href="/froogle?sampleq=1&q=breadmaker">breadmaker</a></td>

<td width=20 nowrap><a href="/froogle?sampleq=1&q=sharp+lcd+tv">sharp lcd tv</a></td>

<td width=20 nowrap><a href="/froogle?sampleq=1&q=tire+pressure+gauge">tire pressure gauge</a></td>

<td width=20 nowrap><a href="/froogle?sampleq=1&q=chrome+rims">chrome rims</a></td>

<td width=20 nowrap><a href="/froogle?sampleq=1&q=napkin+holder">napkin holder</a></td>

<tr>

<td width=20 nowrap><a href="/froogle?sampleq=1&q=outdoor+fireplace">outdoor fireplace</a></td>

<td width=20 nowrap><a href="/froogle?sampleq=1&q=black+pearls">black pearls</a></td>

<td width=20 nowrap><a href="/froogle?sampleq=1&q=chinese+cleaver">chinese cleaver</a></td>

<tr>

<td width=20 nowrap><a href="/froogle?sampleq=1&q=chanel+no+5">chanel no 5</a></td>

<td width=20 nowrap><a href="/froogle?sampleq=1&q=spatula">spatula</a></td>

<td width=20 nowrap><a href="/froogle?sampleq=1&q=humidifier">humidifier</a></td>

<tr>

<td width=20 nowrap><a href="/froogle?sampleq=1&q=camouflage+pants">camouflage pants</a></td>

<td width=20 nowrap><a href="/froogle?sampleq=1&q=hole+punch">hole punch</a></td>

<td width=20 nowrap><a href="/froogle?sampleq=1&q=mobiles">mobiles</a></td>

<td width=20 nowrap><a href="/froogle?sampleq=1&q=bikini">bikini</a></td>

<td width=20 nowrap><a href="/froogle?sampleq=1&q=escaflowne+dvd">escaflowne dvd</a></td>

</table>

<tr>

<a href="Google Home http://www.google.com/">Google Home</a> - <a href="/froogle/base.html">Information for Sellers</a> - <a href="Froogle Tour http://www.google.com/froogle/tour/index.html?promo=homepage">Froogle Tour</a> - <a href=http://www.google.com/about.html>About Google</a>

</table>

</center>

</body>

</html>

JuanBob SSCertifiable Points: 5059 More actions · Answer 8

This is an old post but I'll give this a try.

I have used the stored procedure provided (thank you very much), but I am having trouble with the authorization. I am not using the SetCredentials, but rather passing in SetRequestHeader for authorization. Here is my code:

set @Doing = 'SetRequestHeader("Authorization", "Basic kd7Guer...")'

exec @rc = master.dbo.sp_OAMethod @http,@Doing

if @rc <> 0 goto Error

I have placed it above the Content-Type. The ResponseText is a page stating: This request requires HTTP authentication ().

I know I have the correct code within the "Basic" string. Could anyone provide some help on this one?

Thanks!